Forum Stats

  • 3,769,482 Users
  • 2,252,969 Discussions
  • 7,875,047 Comments

Discussions

PIVOT SQL

a47c623f-b4ee-43f6-94ef-f926b9e097dd
edited Jun 19, 2017 12:32PM in SQL & PL/SQL

Hello Team,

I was trying to query the table for dynamic columns from rows.

I am good with the following example below,

create table a_table(id_number number, dept_id number, dept_name varchar2(40));

insert into a_table(id_number,dept_id,dept_name) values(1,10,'Eng');

insert into a_table(id_number,dept_id,dept_name) values(2,20,'Bio');

insert into a_table(id_number,dept_id,dept_name) values(3,10,'Eng');

insert into a_table(id_number,dept_id,dept_name) values(4,30,'Mat');

select * from a_table;

ID_NUMBERDEPT_IDDEPT_NAME
110Eng
220Bio
310Eng
430Mat

SELECT * FROM

(

  SELECT id_number, dept_id,dept_name

  FROM a_table

)

PIVOT

(

  max(dept_name)

  FOR dept_id IN (10, 20, 30)

);

ID_NUMBER102030
1Eng
2Bio
4Mat
3Eng

Question: Pivot works fine if I send static data into FOR statement of the query.

But I need to pass the dynamic data so that the column names should be dynamic.

For example if I have 100 departments I want to extract the data with heading as 100 departments as column names.

Any help for this question would be greatly appreciated. Thank You.

Regards,

Anil Kumar.

John StegemanmathguyFrank KulashPaulzip

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 41,491 Red Diamond
    edited Jun 19, 2017 11:13AM

    If I had a dollar for every time someone asks about dynamic columns and pivoting, I'd be able to retire.

    Read the FAQ:

    and ask yourself, if you have a dynamic number of columns with dynamic names.... how are you then going to use the results of that query?  You don't know what column names you have available to reference in your code, or how many columns there are.

    My answer is simple... use the right tool... a reporting tool.  The only place you need to dynamically have your data represented is when you are generating reports.  Before that, for any data processing, you typically don't need to pivot your data in such a dynamic way.

    John StegemanmathguyFrank KulashPaulzip
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,223 Red Diamond
    edited Nov 17, 2020 11:09AM

    Hi,

    Blushadow is right (as usual); this is a job for a reporting tool.

    If you must do it in pure SQL, then you'll need dynamic SQL if you need a separate column for each distinct value of  dept_id that is found in the table.

    An alternative is to produce a huge VARCHAR2 column (called txt in the query below) that is formatted to look like separate columns.

    For example:

    <p>WITH&nbsp; &nbsp; all_dept_ids&nbsp; &nbsp; AS</p><p>(</p><p>&nbsp; &nbsp; SELECT&nbsp; &nbsp; dept_id</p><p>&nbsp; &nbsp; ,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ROW_NUMBER () OVER (ORDER BY&nbsp; dept_id)&nbsp; AS col_number</p><p>&nbsp; &nbsp; FROM&nbsp; &nbsp; &nbsp; a_table</p><p>&nbsp; &nbsp; GROUP BY&nbsp; dept_id</p><p>)</p><p>,&nbsp; &nbsp; union_results&nbsp; &nbsp; AS</p><p>(</p><p>&nbsp; &nbsp; SELECT&nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AS part_number</p><p>&nbsp; &nbsp; ,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;t.id_number</p><p>&nbsp; &nbsp; ,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;TO_CHAR (t.id_number, '99999999')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AS id_number_str</p><p>&nbsp; &nbsp; ,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;LISTAGG ( LPAD ( NVL (t.dept_name, ' ')</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;, 10</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;)</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , ' '</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) WITHIN GROUP (ORDER BY d.col_number)&nbsp; AS txt</p><p>&nbsp; &nbsp; FROM&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;all_dept_ids&nbsp; d</p><p>&nbsp; &nbsp; LEFT OUTER JOIN&nbsp; a_table&nbsp; &nbsp; &nbsp; &nbsp;t&nbsp; PARTITION BY (t.id_number)</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ON&nbsp; t.dept_id&nbsp; = d.dept_id</p><p>&nbsp; &nbsp; GROUP BY&nbsp; t.id_number</p><p>UNION ALL</p><p>&nbsp; &nbsp; SELECT&nbsp; &nbsp; 1</p><p>&nbsp; &nbsp; ,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;NULL</p><p>&nbsp; &nbsp; ,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'ID_NUMBER'</p><p>&nbsp; &nbsp; ,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;LISTAGG ( LPAD ( TO_CHAR (dept_id)</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;, 10</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;)</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , ' '</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) WITHIN GROUP (ORDER BY&nbsp; col_number)</p><p>&nbsp; &nbsp; FROM&nbsp; &nbsp; &nbsp; all_dept_ids</p><p>UNION ALL</p><p>&nbsp; &nbsp; SELECT&nbsp; &nbsp; 2</p><p>&nbsp; &nbsp; ,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;NULL</p><p>&nbsp; &nbsp; ,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'---------'</p><p>&nbsp; &nbsp; ,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;LISTAGG ( LPAD ('-', 10, '-')</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , ' '</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) WITHIN GROUP (ORDER BY&nbsp; col_number)</p><p>&nbsp; &nbsp; FROM&nbsp; &nbsp; &nbsp; all_dept_ids</p><p>)</p><p>SELECT&nbsp; &nbsp; id_number_str</p><p>,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;txt</p><p>FROM&nbsp; &nbsp; &nbsp; union_results</p><p>ORDER BY&nbsp; part_number</p><p>;</p>

    Output:

    ID_NUMBER TXT
    --------- --------------------------------------------------
    ID_NUMBER         10         20         30
    --------- ---------- ---------- ----------
            1        Eng
            2                   Bio
            3        Eng
            4                              Mat
    6 rows selected.

    Notice that the result set contains 2 columns and 6 rows: 2 "header" rows, and 4 data rows, shown in black above.  The output shown in red above is added when I run this in SQL*Plus.  Use your front end to avoid displaying lines like those.  (In SQL*Plus, that would be  SET HEADING OFF and  SET FEEDBACK OFF  .)

    Here, I used 10 as the maximum dept_name length.  You can change that to 40, to match the actual column length, or any other value.   LISTAGG returns a VARCHAR2, so with 10 characters per name, plus 1 space to separate the virtual columns, you can have up to TRUNC (4000 / (10 + 1) ) =

    363 virtual columns in a 4000-character VARCHAR2.  With 40 characters per name, you could have 97 virtual columns.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,223 Red Diamond

    When this site was changed in 2020, the reply above got garbled. Here's a formatted version of the same query:

    WITH  all_dept_ids  AS
    (
    	SELECT   dept_id
    	,  	 ROW_NUMBER () OVER (ORDER BY dept_id) AS col_number
    	FROM  	 a_table
    	GROUP BY dept_id
    )
    ,  union_results  AS
    (
    	SELECT   3						 AS part_number
    	,   	 t.id_number
    	,   	 TO_CHAR (t.id_number, '99999999')		 AS id_number_str
    	,   	 LISTAGG ( LPAD ( NVL (t.dept_name, ' ')
     		 	  	 , 10
     				 )
    			 , ' '
    			 ) WITHIN GROUP (ORDER BY d.col_number) AS txt
            FROM        all_dept_ids d
            LEFT OUTER JOIN a_table  t PARTITION BY (t.id_number)
    	   	  		   ON t.dept_id = d.dept_id
     	GROUP BY t.id_number
    UNION ALL
            SELECT   1
    	,   	 NULL
    	,   	 'ID_NUMBER'
    	,   	 LISTAGG ( LPAD ( TO_CHAR (dept_id)
     		 	  	 , 10
     		 		 )
    			 , ' '
    			 ) WITHIN GROUP (ORDER BY col_number)
    	FROM  all_dept_ids
    UNION ALL
           SELECT  2
           ,       NULL
           ,       '---------'
           ,       LISTAGG ( LPAD ('-', 10, '-')
       	     	       , ' '
       		       ) WITHIN GROUP (ORDER BY col_number)
           FROM    all_dept_ids
    )
    SELECT   id_number_str
    ,   	 txt
    FROM  	 union_results
    ORDER BY part_number
    ;
    


This discussion has been closed.