Home » SQL & PL/SQL » SQL & PL/SQL » rows to columns with dynamic allocation column names (10.2.4.0)
rows to columns with dynamic allocation column names [message #570848] Fri, 16 November 2012 07:43 Go to next message
grpatwari
Messages: 198
Registered: June 2008
Location: Hyderabad
Senior Member
Hi,

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.


Please advice.
Re: rows to columns with dynamic allocation column names [message #570850 is a reply to message #570848] Fri, 16 November 2012 08:06 Go to previous messageGo to next message
BlackSwan
Messages: 22793
Registered: January 2009
Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: rows to columns with dynamic allocation column names [message #570852 is a reply to message #570848] Fri, 16 November 2012 08:38 Go to previous messageGo to next message
Michel Cadot
Messages: 59150
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

And from one of your previous topics:

Michel Cadot wrote on Mon, 16 July 2012 09:29
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
...


Regards
Michel


Re: rows to columns with dynamic allocation column names [message #570931 is a reply to message #570852] Mon, 19 November 2012 00:50 Go to previous messageGo to next message
grpatwari
Messages: 198
Registered: June 2008
Location: Hyderabad
Senior Member
Sorry..

I want the o/p like pivoting (rows to columns) with dynamic generation of column names based on the column value.
Re: rows to columns with dynamic allocation column names [message #570932 is a reply to message #570931] Mon, 19 November 2012 01:21 Go to previous messageGo to next message
Michel Cadot
Messages: 59150
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 16 November 2012 15:38
With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

And from one of your previous topics:

Michel Cadot wrote on Mon, 16 July 2012 09:29
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
...


Regards
Michel



Re: rows to columns with dynamic allocation column names [message #570954 is a reply to message #570932] Mon, 19 November 2012 05:05 Go to previous messageGo to next message
grpatwari
Messages: 198
Registered: June 2008
Location: Hyderabad
Senior Member
Hi,

I have below two tables

create table DETAILED_PORT(DN VARCHAR2(16),Circuit_ID VARCHAR2(50),ABR_LOCALNET VARCHAR2(6),
ABR_SUBNET VARCHAR2(3),ABR_RACK NUMBER(3),ABR_SUBRACK NUMBER(3));

create table DETAILED_PORT_INFO ( DN VARCHAR2(16),ETHCLIFC_ID NUMBER);

INSERT INTO DETAILED_PORT(dn,Circuit_ID,...) VALUES('10010010','C1',...);

INSERT INTO DETAILED_PORT(dn,Circuit_ID) VALUES('10010020','C2',...);

INSERT INTO DETAILED_PORT_INFO(dn,ETHCLIFC_ID ) VALUES('10010010',10);
INSERT INTO DETAILED_PORT_INFO(dn,ETHCLIFC_ID ) VALUES('10010010',20);

INSERT INTO DETAILED_PORT_INFO(dn,ETHCLIFC_ID ) VALUES('10010020',100);
INSERT INTO DETAILED_PORT_INFO(dn,ETHCLIFC_ID ) VALUES('10010020',200);
INSERT INTO DETAILED_PORT_INFO(dn,ETHCLIFC_ID ) VALUES('10010020',300);
INSERT INTO DETAILED_PORT_INFO(dn,ETHCLIFC_ID ) VALUES('10010020',400);

o/p:

DN Circuit_ID ABR_LOCALNET ABR_SUBNET ABR_RACK ABR_SUBRACK LIFC_ID1 LIFC_ID2 LIFC_ID3 LIFC_ID4
10010010 C1 10 20
10010020 C2 100 200 300 400

Here DN is having maximum 4 ETHCLIFC_ID values for all the DNs.
Need to take the ETHCLIFC_ID maximum columns and generate the dynamic column headers like LIFC_ID1 till maximum value.

Please advice.
Re: rows to columns with dynamic allocation column names [message #570957 is a reply to message #570954] Mon, 19 November 2012 05:20 Go to previous messageGo to next message
Michel Cadot
Messages: 59150
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 19 November 2012 08:21
Michel Cadot wrote on Fri, 16 November 2012 15:38
With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

And from one of your previous topics:

Michel Cadot wrote on Mon, 16 July 2012 09:29
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
...


Regards
Michel


Re: rows to columns with dynamic allocation column names [message #570959 is a reply to message #570957] Mon, 19 November 2012 05:32 Go to previous messageGo to next message
grpatwari
Messages: 198
Registered: June 2008
Location: Hyderabad
Senior Member
I have below two tables

DETAILED_PORT(DN VARCHAR2(16),Circuit_ID VARCHAR2(50),ABR_LOCALNET VARCHAR2(6),
ABR_SUBNET VARCHAR2(3),ABR_RACK NUMBER(3),ABR_SUBRACK NUMBER(3));

DETAILED_PORT_INFO ( DN VARCHAR2(16),ETHCLIFC_ID NUMBER);

DN Circuit_ID ABR_LOCALNET ABR_SUBNET ABR_RACK ABR_SUBRACK
10010010 C1 AL1 AS1 AR1 ASR1
10010020 C2 AL2 AS2 AR2 ASR2
----------------------------

DN ETHCLIFC_ID
10010010 10
10010010 20
10010020 100
10010020 200
10010020 300
10010020 400

------------------------

O/P:

DN Circuit_ID ABR_LOCALNET ABR_SUBNET ABR_RACK ABR_SUBRACK LIFC_ID1 LIFC_ID2 LIFC_ID3 LIFC_ID4
10010010 C1 AL1 AS1 AR1 ASR1 10 20
10010020 C2 AL2 AS2 AR2 ASR2 100 200 300 400




Re: rows to columns with dynamic allocation column names [message #570962 is a reply to message #570959] Mon, 19 November 2012 06:07 Go to previous messageGo to next message
Michel Cadot
Messages: 59150
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 19 November 2012 12:20
Michel Cadot wrote on Mon, 19 November 2012 08:21
Michel Cadot wrote on Fri, 16 November 2012 15:38
With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

And from one of your previous topics:

Michel Cadot wrote on Mon, 16 July 2012 09:29
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify....


Regards
Michel




If there is anything you don't understand in this, please tell us.

Regards
Michel
Re: rows to columns with dynamic allocation column names [message #571003 is a reply to message #570962] Tue, 20 November 2012 00:53 Go to previous messageGo to next message
grpatwari
Messages: 198
Registered: June 2008
Location: Hyderabad
Senior Member
I have below two tables

CREATE TABLE detailed_port
(
dn VARCHAR2(16),
circuit_id VARCHAR2(50),
abr_localnet VARCHAR2(6),
abr_subnet VARCHAR2(3),
abr_rack NUMBER(3),
abr_subrack NUMBER(3)
);

CREATE TABLE detailed_port_info
(
dn VARCHAR2(16),
ethclifc_id NUMBER
);

Data should be as follows.

DN Circuit_ID ABR_LOCALNET ABR_SUBNET ABR_RACK ABR_SUBRACK
10010010 C1 AL1 AS1 AR1 ASR1
10010020 C2 AL2 AS2 AR2 ASR2

DN ETHCLIFC_ID
10010010 10
10010010 20
10010020 100
10010020 200
10010020 300
10010020 400

------------------------

O/P:

DN Circuit_ID ABR_LOCALNET ABR_SUBNET ABR_RACK ABR_SUBRACK LIFC_ID1 LIFC_ID2 LIFC_ID3 LIFC_ID4
----------------------------------------------------------------------------------------------------
10010010 C1 AL1 AS1 AR1 ASR1 10 20
10010020 C2 AL2 AS2 AR2 ASR2 100 200 300 400

Here DN is having maximum 4 ETHCLIFC_ID values for all the DNs.
Need to take the ETHCLIFC_ID maximum columns and generate the dynamic column headers like LIFC_ID1 till maximum value.


Please advice.
Re: rows to columns with dynamic allocation column names [message #571004 is a reply to message #571003] Tue, 20 November 2012 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 59150
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 19 November 2012 13:07
Michel Cadot wrote on Mon, 19 November 2012 12:20
Michel Cadot wrote on Mon, 19 November 2012 08:21
Michel Cadot wrote on Fri, 16 November 2012 15:38
With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

And from one of your previous topics:

Michel Cadot wrote on Mon, 16 July 2012 09:29
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify....


Regards
Michel




If there is anything you don't understand in this, please tell us.

Regards
Michel

Re: rows to columns with dynamic allocation column names [message #571016 is a reply to message #571004] Tue, 20 November 2012 02:54 Go to previous messageGo to next message
grpatwari
Messages: 198
Registered: June 2008
Location: Hyderabad
Senior Member
I could not able to indent the data.

Please suggest.
Re: rows to columns with dynamic allocation column names [message #571019 is a reply to message #571016] Tue, 20 November 2012 03:54 Go to previous messageGo to next message
Michel Cadot
Messages: 59150
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you didn't read the link. Do you understand what is the "code tag"?
You have a "Test" forum at the bottom of forum main page to practice.

Regards
Michel
Re: rows to columns with dynamic allocation column names [message #571024 is a reply to message #571016] Tue, 20 November 2012 04:25 Go to previous messageGo to next message
Maaher
Messages: 7053
Registered: December 2001
Senior Member
The setup:
CREATE TABLE detailed_port( dn VARCHAR2(16)
                          , circuit_id VARCHAR2(5)
                          )
/

CREATE TABLE detailed_port_info( dn VARCHAR2(16)
                               , eth_id NUMBER
                               )
/

Insert Into detailed_port(DN, circuit_id) values ('10010010', 'C1');
Insert Into detailed_port(DN, circuit_id) values ('10010020', 'C2');


Insert Into detailed_Port_Info(dn, eth_id) values ('10010010',  10);
Insert Into detailed_Port_Info(dn, eth_id) values ('10010010',  20);
Insert Into detailed_Port_Info(dn, eth_id) values ('10010020', 100);
Insert Into detailed_Port_Info(dn, eth_id) values ('10010020', 200);
Insert Into detailed_Port_Info(dn, eth_id) values ('10010020', 300);
Insert Into detailed_Port_Info(dn, eth_id) values ('10010020', 400);


Note that I've simplified the tables somewhat. The extra columns have no impact on the solution.

Since you did not explain the requirements I had to go by your example. I came to the following conclusion:
1. You want a record for each DN
2. For each DN you want all info records as columns, sorted by the eth_id


Now, we want to pivot the rows to columns. This can be done, but it requires some steps.
1. We add some kind of rank (based on eth_id). This is the DENSE_RANK() part.
2. We wrap the output and use the "rn" (rank) column as source for several similar decodes. Note that the output is limited to 5 info records. If you need more, you should add extra decodes.
3. We use a group function to get only one record for each DN.

I used this query for testing:
Select p.dn
     , p.circuit_id c_id
     , v.eth_id_1
     , v.eth_id_2
     , v.eth_id_3
     , v.eth_id_4
     , v.eth_id_5     
From   detailed_port p
   ,   ( Select dn
              , max(decode(rn, 1, eth_id)) eth_id_1
              , max(decode(rn, 2, eth_id)) eth_id_2
              , max(decode(rn, 3, eth_id)) eth_id_3
              , max(decode(rn, 4, eth_id)) eth_id_4
              , max(decode(rn, 5, eth_id)) eth_id_5
         From ( Select i.dn
                     , i.eth_id
                     , dense_rank() over ( partition by i.dn order by i.eth_id) rn
                From detailed_port_info i
              )
         Group By dn
        ) v
Where  p.dn = v.dn
/


I got this output:
SQL> Select p.dn
  2       , p.circuit_id c_id
  3       , v.eth_id_1
  4       , v.eth_id_2
  5       , v.eth_id_3
  6       , v.eth_id_4
  7       , v.eth_id_5
  8  From   detailed_port p
  9     ,   ( Select dn
 10                , max(decode(rn, 1, eth_id)) eth_id_1
 11                , max(decode(rn, 2, eth_id)) eth_id_2
 12                , max(decode(rn, 3, eth_id)) eth_id_3
 13                , max(decode(rn, 4, eth_id)) eth_id_4
 14                , max(decode(rn, 5, eth_id)) eth_id_5
 15           From ( Select i.dn
 16                       , i.eth_id
 17                       , dense_rank() over ( partition by i.dn order by i.eth_id) rn
 18                  From detailed_port_info i
 19                )
 20           Group By dn
 21          ) v
 22  Where  p.dn = v.dn
 23  /

DN               C_ID    ETH_ID_1   ETH_ID_2   ETH_ID_3   ETH_ID_4   ETH_ID_5
---------------- ----- ---------- ---------- ---------- ---------- ----------
10010010         C1            10         20
10010020         C2           100        200        300        400

SQL>


Was that what you were looking for?

MHE

Edit: please do read Michel's reply. Use [code] tags to get a clean code sample. Provide create table and insert statements so we all have the same starting point. You can use the "Test" forum to check whether the post layout is ok.

[Updated on: Tue, 20 November 2012 04:27]

Report message to a moderator

Re: rows to columns with dynamic allocation column names [message #571037 is a reply to message #571024] Tue, 20 November 2012 05:50 Go to previous messageGo to next message
grpatwari
Messages: 198
Registered: June 2008
Location: Hyderabad
Senior Member
Maahar, Thank you very much for this reply.

Output is correct but I dont want to hardcode the columns means Columns are not statis and it should be dynamic.

But I want to generate the column headers dynamically because I don't know how many columns I will get.

Please advice.
Re: rows to columns with dynamic allocation column names [message #571038 is a reply to message #571037] Tue, 20 November 2012 05:57 Go to previous messageGo to next message
Michel Cadot
Messages: 59150
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a FAQ, please search.

Regards
Michel
Re: rows to columns with dynamic allocation column names [message #571039 is a reply to message #571038] Tue, 20 November 2012 06:01 Go to previous messageGo to next message
Maaher
Messages: 7053
Registered: December 2001
Senior Member
Well, SQL needs to know what you want to select, what columns you want in your output. One option I can think of is to concatenate the info records instead of displaying them as separate columns. If you explain what you want to accomplish with this "dynamic" select, we could offer some options.

Did you search the board for similar questions?

MHE
Re: rows to columns with dynamic allocation column names [message #571041 is a reply to message #571039] Tue, 20 November 2012 06:36 Go to previous messageGo to next message
Maaher
Messages: 7053
Registered: December 2001
Senior Member
A ref cursor might help you, though. The setup is the same as in my initial test (create table + insert). I created this procedure. Note, it's a quick and dirty example, as a proof of concept. You might want to add exception handling etc.
Create procedure get_info_orafaq(p_cur in out sys_refcursor)
Is
  v_sel_port Varchar2(1000);
  v_sel_info Varchar2(1000);
  
  v_maxcnt   PLS_INTEGER;
Begin
  v_sel_port := 'Select p.dn';
  v_sel_info := '( select dn';
  
  -- 1. get the max number of distinct values per DN.
  Select max(cnt)
  Into   v_maxcnt
  From   (select count(eth_id) cnt
          from   detailed_port_info
          group  by dn
         );
     
   -- We make sure that we have enough columns in our ref cursor     
   For i in 1..v_maxcnt Loop
     -- Extend the outer select 
     v_sel_port := v_sel_port||chr(10)||', v.eth_id_'||to_char(i);
     
     -- Extend the inner select
     v_sel_info := v_sel_info||chr(10)||', max(decode(rn, '||to_char(i)||', eth_id)) eth_id_'||to_char(i);
   End Loop;
   
   -- Finalize the inner select
   v_sel_info := v_sel_info||chr(10)||'From ( Select i.dn
                     , i.eth_id
                     , dense_rank() over ( partition by i.dn order by i.eth_id) rn
                From detailed_port_info i
              )
         Group By dn
        ) v';
   
   -- Finalize the outer select
   v_sel_port := v_sel_port||chr(10)||'From detailed_port p
   ,   '||v_sel_info||' Where  p.dn = v.dn';
   
   -- Open a ref cursor
   open p_cur for v_sel_port;
End get_info_orafaq;
/
sho err 


Then we test our procedure:
SQL> var rc refcursor
SQL>
SQL> exec get_info_orafaq(:rc);

PL/SQL procedure successfully completed.

SQL> print rc

DN                 ETH_ID_1   ETH_ID_2   ETH_ID_3   ETH_ID_4
---------------- ---------- ---------- ---------- ----------
10010010                 10         20
10010020                100        200        300        400

SQL>
SQL> rem add two extra rows. We now want extra colums in our output
SQL> Insert Into detailed_Port_Info(dn, eth_id) values ('10010020', 500);

1 row created.

SQL> Insert Into detailed_Port_Info(dn, eth_id) values ('10010020', 600);

1 row created.

SQL>
SQL> exec get_info_orafaq(:rc);

PL/SQL procedure successfully completed.

SQL>
SQL> print rc

DN                 ETH_ID_1   ETH_ID_2   ETH_ID_3   ETH_ID_4   ETH_ID_5  ETH_ID_6
---------------- ---------- ---------- ---------- ---------- ---------- ----------
10010010                 10         20
10010020                100        200        300        400        500       600


SQL>


So, by using a ref cursor you might be able to get around this.
MHE
Re: rows to columns with dynamic allocation column names [message #571043 is a reply to message #571041] Tue, 20 November 2012 06:54 Go to previous message
grpatwari
Messages: 198
Registered: June 2008
Location: Hyderabad
Senior Member
Thank you very much for your reply.

I will check from my side.
Previous Topic: Best way to get several rows from SCD for each key
Next Topic: design help
Goto Forum:
  


Current Time: Mon Sep 22 03:35:12 CDT 2014

Total time taken to generate the page: 0.09881 seconds