How to display row data in column dynamically in Oracle SQL

Question

I have a table data in the following format in Oracle:

Roll_No Paper Code    Inst_Code
1       71            10
1       72            10
1       73            10
2       71            10
2       73            10
2       75            10
3       72            11
3       73            11
4       71            11
4       73            11

and so on. I want to display the data in the following format dynamically...

Inst_Code    71     72      73     75
--------     ---    ---     ---    ---
10           2      1       2      1
11           1      1       2      0

So if somebody inserts paper code 78, you suddenly expect column 78 to show up? — jarlh
– jarlh, Commented Jan 21, 2016 at 11:18
i suspect it is select paper_code, inst_code, count(*) from t group by paper_code, inst_code and pivot by paper_code values ? Pivot columns 71, 72 ... cannot be defined dynamically. — Frank Ockenfuss
– Frank Ockenfuss, Commented Jan 21, 2016 at 11:33

Frank Ockenfuss · Accepted Answer · 2016-01-21 11:44:53Z

3

with t(Roll_No,
Paper_Code,
Inst_Code) as
 (          select 1, 71, 10 from dual
  union all select 1, 72, 10 from dual
  union all select 1, 73, 10 from dual
  union all select 2, 71, 10 from dual
  union all select 2, 73, 10 from dual
  union all select 2, 75, 10 from dual
  union all select 3, 72, 11 from dual
  union all select 3, 73, 11 from dual
  union all select 4, 71, 11 from dual
  union all select 4, 73, 11 from dual)

select *
  from (select paper_code, inst_code, count(*) total
          from t
         group by paper_code, inst_code) pivot(sum(total) for paper_code in(71,
                                                                            72,
                                                                            73,
                                                                            75));

answered Jan 21, 2016 at 11:44

Frank Ockenfuss

2,04312 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3553260 Over a year ago

Please, explain why you used union all select. Also, 'with' what does that mean?

Aleksej · Accepted Answer · 2016-01-21 12:38:41Z

I don't believe you can build an SQL doing that, given that you have a variable number or columns. The best I could find is some dynamic SQL that builds your query:

set serveroutput on
DECLARE
    vSQL              VARCHAR2(1000) :=
     'SELECT *
      FROM   (SELECT paper_code, inst_node
              FROM   test)
      PIVOT  (count(1)  FOR (paper_code) IN (PaperCodeList))
      order by inst_node';
    vPaperCodeList    VARCHAR2(1000);
BEGIN
    SELECT LISTAGG(paper_code || ' as "' || paper_code ||'"', ', ')  WITHIN GROUP (ORDER BY paper_code)  as list
      INTO vPaperCodeList
      FROM (SELECT DISTINCT paper_code FROM test);

    vSQL      := REPLACE(
                         vSQL,
                         'PaperCodeList',
                         vPaperCodeList
                        );
    dbms_output.put_line(vSQL);
END;
/

This will build a query like the following:

SQL> SELECT *
  2        FROM   (SELECT paper_code, inst_node
  3                FROM   test)
  4        PIVOT  (count(1)  FOR (paper_code) IN (71 as "71", 72 as "72", 73 as "73", 75 as "75"))
  5        order by inst_node;

 INST_NODE         71         72         73         75
---------- ---------- ---------- ---------- ----------
        10          2          1          2          1
        11          1          1          2          0

Jemo · Accepted Answer · 2016-01-21 14:34:45Z

Franks answer won't work with dynamic list of columns as for pivot you have to declare all of them, thus their number can't vary. Another option than dynamically building SQL is to use XML (it can use "in ANY"):

SELECT *
     FROM (
            SELECT Roll_No,
                 Paper_Code,
                 Inst_Code
            FROM t
            )
            PIVOT XML(COUNT(Roll_No) AS "Value" FOR paper_code IN (ANY))

This way the output would be 2 columns: inst_code and paper_code_xml where you have all the paper_counts with their values:

 INST_CODE PAPER_CODE_XML
 10 <XMLTYPE>
 11 <XMLTYPE>

where the xml value is:

<PivotSet>
<item>
    <column name = "PAPER_CODE">71</column>
    <column name = "Value">2</column>
</item>
<item>
    <column name = "PAPER_CODE">72</column>
    <column name = "Value">1</column>
</item>
<item>
    <column name = "PAPER_CODE">73</column>
    <column name = "Value">2</column>
</item>
<item>
    <column name = "PAPER_CODE">75</column>
    <column name = "Value">1</column>
</item>

Collectives™ on Stack Overflow

How to display row data in column dynamically in Oracle SQL

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related