SQL & PL/SQL :: Rows To Columns With Dynamic Allocation Column Names

Nov 16, 2012

I want the output from two tables with rows to columns and generate dynamic basing on the columns.

For example: Table A (a1) , Table B(a1,b1)

Data: A B
------ ----
a1 a1 b1
--- ---- ----
1 1 x
2 1 y
3 2 a
2 b
2 c

o/p: Columnname col_1 col_2 col_3
-----------------------------------------------
a1 b1_1 b1_2 b1_3
----- ---------------------
1 x y
2 a b c

Columns should be generated based on the second table second column.

View 18 Replies


ADVERTISEMENT

PL/SQL :: How To Use Column Names Generated From Dynamic SQL

Feb 8, 2013

I have a problem with Dynamic SQL.

I have written an SQL which will dynamically generate the Select statement with from and where clause in it.

But that select statement when executed will get me hundreds of rows and i want to insert each row separately into one more table.

For that i have used a ref cursor to open and insert the table.

In the select list the column names will also be as follows: COLUMN1, COLUMN2, COLUMN3,....COLUMNn

find below the sample code:

TYPE ref_csr IS REF CURSOR;

insert_csr ref_csr;

v_select VARCHAR2 (4000) := NULL;
v_table VARCHAR2 (4000) := NULL;
v_where VARCHAR2 (4000) := NULL;
v_ins_tab VARCHAR2 (4000) := NULL;
v_insert VARCHAR2 (4000) := NULL;
v_ins_query VARCHAR2 (4000) := NULL;

[Code]...

How to fetch the column names here
|| ');';

EXECUTE IMMEDIATE v_ins_query;
END LOOP;

View 8 Replies View Related

SQL & PL/SQL :: How To Use Column Names Generated From Dynamic Query

Feb 8, 2013

I have written an SQL which will dynamically generate the Select statement with from and where clause in it. But that select statement when executed will get me hundreds of rows and i want to insert each row separately into one more table.

For that i have used a ref cursor to open and insert the table.

In the select list the column names will also be as follows: COLUMN1, COLUMN2, COLUMN3,....COLUMNn

find below the sample

TYPE ref_csr IS REF CURSOR;
insert_csr ref_csr;
v_select VARCHAR2 (4000) := NULL;

[Code].....

View 5 Replies View Related

SQL & PL/SQL :: How To Query For Columns Without Using Column Names

Jun 5, 2013

I have limited permissions and am unable to create temp tables.So I would like to use a cursor to "create" a table of sorts then access/query it. But this "table"/cursor would have no column names so how do I refer to the columns? Is there a way to refer to a column by column number rather than column name in a query:

select column1 from tablename where column2 = 'abc'?

Is there a way in a query/update/insert to refer to a column by column number rather than column name?

declare
cursor c1 is
select 'abc', '8-Apr-2013', pk_id from EMPLOYEE where pk_id = '153'
UNION
select '1xyz', '4-10-2013', pk_id from EMPLOYEE where pk_id = '154'
c1_val number;

[code]....

View 4 Replies View Related

SQL & PL/SQL :: Transpose Dynamic Rows To Columns

Jul 15, 2013

I have a result table from a select query. Create_table.txt

FDAVAILABILITY_2GAVAILABILITY_3GTCH_TRAFFICHOSRCSSR
2013 Jun0.9870.992928359.09430.98360.975
2013 W270.99210.994328512.48650.98320.9756
2013 W280.99320.993728659.32560.98430.9734
2013 W290.99530.993328355.02630.98480.9741

I need to transpose this table to below one. But since my rows in 1 table is dynamic and it will increment each week and month I couldn't get a correct result from unpivot.

2013 Jun2013 W272013 W282013 W29
AVAILABILITY_2G0.99 0.99 0.99 1.00
AVAILABILITY_3G0.99 0.99 0.99 0.99
TCH_TRAFFIC28359.094328512.486528659.325628355.0263
HOSR 0.9836 0.9832 0.9843 0.9848
CSSR 0.975 0.9756 0.9734 0.9741

Is it possible to create above table from attached , with a select statement?

View 7 Replies View Related

SQL & PL/SQL :: How To Pivot Table So That Rows Become Column Names

May 9, 2012

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit
PL/SQL Release 10.2.0.5.0 - Production
"CORE10.2.0.5.0Production"
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

See attachment for table creation and table data.

The table tbl_completed has two columns, id and completed. I have created a query that does provide me with the result set that I need, but I need to pivot the rows into columns. So instead of a result set to look like this:

select completed, count(completed) as theCount from tbl_completed group by completed;

COMPLETED THECOUNT
Y 772
N 720

I need a result set to look like this:

COMPLETED,Y,N
THECOUNT,772,720

The best solution that I have discovered is this, but I figuring their might be a better way.

select
(select 'THECOUNT' from dual) as COMPLETED,
(select count(completed) from tbl_completed where completed = 'Y') as Y,
(select count(completed) from tbl_completed where completed = 'N') as N
from dual;

COMPLETED,Y,N
THECOUNT,772,720

View 3 Replies View Related

SQL & PL/SQL :: Select Dynamic Column Names In Select Statement In Function?

Jul 4, 2010

i want to select dynamic column names in my select statement in my function.

View 4 Replies View Related

SQL & PL/SQL :: Dynamic Rows To Column

May 18, 2010

converting Rows to Column for a specific group.

Following is the query

SELECT Lpad(papf.employee_number, 6, '0') "File#",
papf.full_name,
Decode(ppee.processing_type, 'R', Decode(ppee.element_name,
'Regular Wages USD', peev.screen_entry_value,

'Regular Salary USD', Round(peev.screen_entry_value / 26, 2),
0),

[Code]...

Which gives me out put as

Example 1

Employee Number Name Value Rate Type Type Assignment Id
-----------------------------------------------------------------
4 Employee 1 800 N 5 Yr Bonus 64
4 Employee 1 1063 N 6 Months Bouns 64
4 Employee 1 24.04 H Reg Hourly Sal 64
5 Employee 2 6923.08 S Reg Sal 65
6 Employee 3 5961.54 S Reg Sal 66
6 Employee 3 15000 N 6 Months Bouns 66

I want to convert above out to look like following

Emp Num Name Value Rate Type Type Val 1 Rate Type 1 Type 1 Value 2 Rate Type 2 Type 2 Assignt Id
-------------------------------------------------------------------------------------------------------------------
4 Employee 1 24.04 H Reg Hourly Sal 800 N 5 Yr Bon 1063 N 6 Mon Bon 64
5 Employee 2 6923.08 S Reg Sal 65
6 Employee 3 5961.54 S Reg Sal 15000 N 6 MontBon 66

Example of Query Output 2.

Employee Number Name Value Rate Type Type Assignment Id
--------------------------------------------------------------------------------
4 Employee 1 800 N 5 Yr Bonus 64
4 Employee 1 1063 N 6 Months Bouns 64
4 Employee 1 1345 N Patent Bonus 64
4 Employee 1 24.04 H Reg Hourly Sal 64
5 Employee 2 6923.08 S Reg Sal 65
6 Employee 3 5961.54 S Reg Sal 66
6 Employee 3 15000 N 6 Months Bouns 66

Desired Output from Above Query. As you can see column have increased as one more row for same employee has increased

ENu Nm Val RTy Ty Val1 RTy1 Ty1 Val2 RTy2 Ty2 Val3 RTy3 Ty3 AsgId
-------------------------------------------------------------------------------------------------------------------
4 E1 24.04 H Reg Hour Sal 1063 N 6 Mon Bns 1345 N Pat Bon 800 N 5YB 64
5 E2 6923.08 S Reg Sal 65
6 E3 5961.54 S Reg Sal 15000 N 6 Mos Bns 66

View 7 Replies View Related

SQL & PL/SQL :: Select First 40 Columns Without Giving All Column Names In Select Clause?

Mar 3, 2011

I have a table with around 80 columns. All i need is to select first 40 columns.

Is there any way to select first 40 columns without giving all the 40 Column Names in select clause.

View 2 Replies View Related

Server Administration :: Change Space Allocation For Character Columns In Database

Oct 24, 2012

I want to change space allocation for character columns in my database, So it will store them as 'CHAR' and not 'BYTE'.my character set is

SQL> SELECT VALUE FROM V$NLS_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';
VALUE
----------------------------------------------------------------
AL32UTF8
SQL> alter system set NLS_LENGTH_SEMANTICS='CHAR' scope=both;

System altered.I bounced the instance just to make sure

System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Connected to an idle instance.

SQL>
SQL>
SQL> startup
ORACLE instance started.
[code].....

And then I want to see that when I create a table with some varchar2 column,The space for it will be allocated by chars, and not by bytes! However, when I run a check of create table, this is what I get:

SQL> drop table check_char;

Table dropped.

SQL> create table check_char (some_name varchar2(10));

Table created.

SQL> select a.char_used
2 from all_tab_columns a
3 where table_name='CHECK_CHAR'
4 and a.owner='SYS';

C
-
B
SQL>

What is the reason for space allocation to remain in BYTES and not CHAR, or what else I can check?

View 4 Replies View Related

PL/SQL :: Concatenating Columns (in Multiple Rows) To One Column In Single Row

Nov 28, 2012

column1    column2            column3    column4

12                Mar-21-2005 BDW        blah blah blah
11               Feb-07-2001    ZV            ha ha ha
12                Jan-02-2002   YM           zuck zuck zuckI want a view that has that data like this:

column1    column2         

12                Mar-21-2005 - BDW - blah blah blah; Jan-02-2002 - YM     -      zuck zuck zuck
11               Feb-07-2001    ZV            ha ha haCan you help with SQL ?

I tried to use this Oracle LISTAGG function in the SQL, but got a "string concatenation limit exceeded"

View 3 Replies View Related

SQL & PL/SQL :: Update Column In A Table Which Has 3 Columns Of 16 Million Rows?

Apr 25, 2012

trying to update a column in a table which has 3 columns of 16million rows from column in another table which has 1million rows, there is no relationship between the 2 tables.

Table A has 3 columns of 16million rows, the first two columns have 16million ID numbers, the 3rd colunm is currently NULL.

Table B has 1million Numbers, i need to somehow update column 3 in table A using the numbers in table B, it doesnt how many times each of the 1 million numbers are used but i dont want it to just update every row to the same value.

View 13 Replies View Related

SQL & PL/SQL :: Find All Column Names In All 20 Tables That Have Same Names?

Jul 7, 2010

I have 20 tables. In all 20 tables, some of column names are same and some are different. I need to find all column names in all 20 tables that have same names.

create table t1 (
col1 varchar2(10),
col2 varchar2(10));
create table t2 (
col1 varchar2(10),
col3 varchar2(10));
create table t3 (
col1 varchar2(10));

View 1 Replies View Related

SQL & PL/SQL :: Convert Columns To Rows With Extra Hard Coded Column

Mar 30, 2012

I've a query that sum up data from one table. But I've to convert them into rows.

Summary query result.

COL1 COL2 COL3 COL4
1000 2000 3000 4000

Test Table creation script.

CREATE TABLE TEST_TBL
(
COL1 NUMBER(14,2),
COL2 NUMBER(14,2),
COL3 NUMBER(14,2),
COL4 NUMBER(14,2)
) ;

INSERT INTO TEST_TBL VALUES( 1000, 2000, 3000, 4000 ) ;

Expected result

A1 1000
A2 2000
A3 3000
A4 4000

A1, A2, A3, A4 are hard coded fixed values.

I could have done this but not a good idea in case table TEST_TBL is not a single row table but an inline query on 1,00,00,000 records with summary functions. In my table I've a summary query instead of single row table.

SELECT 'A1', COL1 FROM TEST_TBL
UNION ALL
SELECT 'A2', COL2 FROM TEST_TBL
UNION ALL
SELECT 'A3', COL3 FROM TEST_TBL
UNION ALL
SELECT 'A4', COL4 FROM TEST_TBL

View 12 Replies View Related

Dynamic Categories Size And Names?

Nov 29, 2010

I need to display dynamically a bunch of categories and I need the SQL algorithm to determine their size and names.

So, I have a table with one column and a variable number of records and I need to display them in GUI in pages, displaying also the page name which should be something like short name for first item to short name of last item in category.

Maximum items per page in 10 but I don't want to simply display blocks of 10 records. If the name of the 8th item starts with B and the name of the 9th item starts with C I want the first page to contain only the first 8 records. Minimum accepted items per page is 8, maximum is 10. The split should be based on the first 2 characters. Page name should be something like: first 4 chars of first item in category - first 4 chars of the last item in category.

View 3 Replies View Related

SQL & PL/SQL :: Dynamic Categories Size And Names

Nov 29, 2010

I need to display dynamically a bunch of categories and I need the SQL algorithm to determine their size and names.

So, I have a table with one column and a variable number of records and I need to display them in GUI in pages, displaying also the page name which should be something like short name for first item to short name of last item in category.

Maximum items per page in 10 but I don't want to simply display blocks of 10 records. If the name of the 8th item starts with B and the name of the 9th item starts with C I want the first page to contain only the first 8 records. Minimum accepted items per page is 8, maximum is 10. The split should be based on the first 2 characters. Page name should be something like: first 4 chars of first item in category - first 4 chars of the last item in category.

View 18 Replies View Related

SQL & PL/SQL :: Dynamic Query For Swapping Names

Mar 25, 2011

writing dynamic sql query for swapping names:

the output shuld be like that

input output

a a
a b b a
a b c c b a
a b c d d c b a

i have writen it in static form by using instr,substr.But i m having difficulty in making it dynamic by using select statement.I have to make it for retrieving data from database.

View 13 Replies View Related

PL/SQL :: How To Transpose A Table From Rows To Columns Or Columns Into Rows

Aug 22, 2012

what are the collections available in Oracle Plsql, what are concepts of collection.

How to Transpose a Table from rows to columns or columns into rows.

DDL and DML concepts.

What is the concepts of statistics in Oracle Plsql.

View 4 Replies View Related

Like Condition - Match Two Columns Containing Names?

Dec 28, 2011

I have a problem with a query I'm trying to run. I need to match two columns containing names, first column (NAME1) contains only the surname and the second column (NAME2) contains a surname and initials, with the initials turning up on either side of the surname.

Example:
NAME1: 'Bush', 'Thomas', 'Cook', 'Smith'
NAME2: 'Bush, B.B.', 'Thomas,C.' 'Cook', 'A.A. Smith'

Basically the code I'm trying only turns up complete matches and not partials. what I'm doing wrong or how I can improve it?

CODEselect *
from TEST
where NAME1 like ('%'||NAME2||'%');

View 4 Replies View Related

SQL & PL/SQL :: Transpose Rows-to-columns And Columns-to-rows

Oct 6, 2010

I need to transpose the following table columns to rows and rows to columns...Im not quite sure how to acheive this...I have the following table with fixed number of columns and dynamic number of rows based on date filter in query

MONTH_YEAR RMS RMS_OCC TTL_RMS
---------------------------------------
SEPTEMBER 200917790017790
OCTOBER 2009183831278818347
NOVEMBER 2009177901460517762

and I need to display this as

COL1 SEPTEMBER 2009 OCTOBER 2009 NOVEMBER 2009
--------------------------------------------------------------
RMS 17790 18383 17790
RMS_OCC 0 12788 14605
TTL_RMS 17790 18347 17762

View 3 Replies View Related

SQL & PL/SQL :: Column Values To Column Names

May 20, 2011

I had a table( T1). Five columns exists in the table. The table data is like below

C1C2C3 C4C5
--- ----- ---- ----- ----
JAN FEB MAR APR MAY
One Two Three Four Five

I need the output like below one

JANFEBMARAPRMAY
---- ----- ----- ---- ----
OneTwoThreeFourFive

View 13 Replies View Related

How To Use Column Names In Array

Dec 2, 2012

I have a tmp table with 6 cols, and data to be inserted is from two other tables. My problem is there the cols of the table are

sid varchar2(10),
cob_dt varchar2(10),
deal_id varchar2(10),
new_val varchar2(10),
old_val varchar2(30),
amend_col v archar2(50),
i have return a proc
create or replace

[Code]...

View 1 Replies View Related

SQL & PL/SQL :: Column Names Are Changed

Jul 21, 2011

when we decribe one table structure, it shows the column name as below:

DUKEDMTS03DB01:SYS@TODS1> desc uet_rep.ECM_MAINTENANCE

Name Null? Type
----------------------------------------------------- -------- ------------------------------------
C1 NOT NULL VARCHAR2(15)
C2 VARCHAR2(254)
C3 NOT NULL NUMBER(15)
C4 VARCHAR2(254)
C5 NOT NULL VARCHAR2(254)
C6 NOT NULL NUMBER(15)
C7 NOT NULL NUMBER(15)
C8 NOT NULL VARCHAR2(254)

[Code]...

But, till yday it was showing the orignal column name..

View 3 Replies View Related

SQL & PL/SQL :: Using VArray For Known Column Names?

May 2, 2013

I am trying to build an array that contains known column names, 63 columns in all.The idea is to search backwards from 63 down to say 10 to find the first non-blank column.

Then using a loop with the known number of lines print from 1 to last non-blank column.The problem is that it only shows the column names and not the values of the columns.So I am getting 63 lines every time, instead of 20, 30, 40 or however how many fields actually have something.

Here is my
declare
to_num number;
field_name varchar2(15);
[code]....

View 9 Replies View Related

SQL & PL/SQL :: Script To Generate Dynamic Columns

Mar 6, 2011

Is there any sql script to generate dynamic columns? pivot still needs the programmer to know the data to generate columns just like this.

select * from
(select current_position, fk_department, wages
from employee)
pivot
(sum(wages)
for fk_department in ('INT', 'WEL', 'CEN', 'POL'));

how about if i don't know the data in fk_department. is this possible in sql?

View 39 Replies View Related

SQL & PL/SQL :: How To Pass Dynamic Columns In Functions

Aug 16, 2010

I am facing with one problem while creation of function.

CREATE OR REPLACE FUNCTION fun1(
v_finyear VARCHAR2,
v_yrno NUMBER,

[Code].....

The function created successfully...

But it return the wrong values, like

SQL> SELECT fun1('2004-05', 8, 'FEB')
2 FROM DUAL
3 ;

TB('2004-05',8,'FEB')
--------------------------------------------------------------------------------
FEB

SQL> spool off;

2004-05 is the value of TABLE1
08 is also value of TABLE1
FEB is the Column_name of TABLE1

Actually, i have a value in FEB month, but it return FEB. FEB is the column_name of 'TABLE1'

View 1 Replies View Related

SQL & PL/SQL :: How To Get Dynamic Columns Using Default EMP Table

Aug 18, 2011

i am practicing to get Dynamic columns as i have learnt from Orafaq SQL/PLSQL forum .i am using Default EMP table...first one is running smoothly as below:

1 DECLARE
2 v_sql VARCHAR2(32767);
3 v_refcur SYS_REFCURSOR;
4 BEGIN
5 v_sql := 'SELECT deptno ';
[code]....

View 11 Replies View Related

SQL & PL/SQL :: Dynamic Column Creation / Create Column Based On Number Of Child In Hierarchy

Oct 15, 2013

I have one hirarchical query which return the parent to child hirarch level data. it has 11 level child data. i want to create column based on number of child in hirarchy. though i know it is 11 but it can change also.Is there any way i can create the column dynamically

ORG_UNITCOST_CENTERORG_UNIT_NAMEPARENT_ORG_UNITLLSYS_CONNECT_BY_PATH(ORG_UNIT,'/')

500171960000022000Managing Director - LUL500169965/00000001/50000001/50017588/50016996/50017196
500018370000021241FSO500171966/00000001/50000001/50017588/50016996/50017196/50001837
502894940000021241Knowledge Management500018377/00000001/50000001/50017588/50016996/50017196/50001837/50289494
508014980000021241Finance500018377/00000001/50000001/50017588/50016996/50017196/50001837/50801498

View 1 Replies View Related

SQL & PL/SQL :: Return Column Names In Lowercase

Oct 11, 2011

I wonder if there is any way to return the columns of an select with its letters lowercase?

I have a piece of code that creates an script wich returns an SQL result to be confronted with some templates. My template have the column names in lowercase and because It is case sensitive the Uppercase returned by Oracle,

View 5 Replies View Related

Forms :: Dynamically Getting Column Names

Jul 20, 2010

i have list item populated with many table names from a schema. i have grid in oracle forms 10g and i want to fill the grid with at least four/more columns.I want to fire the list change trigger when each time any one table name is selected.

how can i find the columns names dynamically for filling the grid.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved