7 Replies Latest reply on Jan 4, 2011 11:56 AM by Frank Kulash

    Column to Row Display


      I have 252 column table data, where in I need to display some of those columns values as rows in my report.

      col1 col2 col3 col4 .... col252

      i want to display this as


      I am unable to use Pivot as I am using Oracle 10g. Pls suggest a solution on this?
        • 1. Re: Column to Row Display
          • 2. Re: Column to Row Display
            query for 10g would be

            select *
            from (select job,
            sum(decode(deptno,10,sal)) dept10,
            sum(decode(deptno,20,sal)) dept20,
            sum(decode(deptno,30,sal)) dept30,
            sum(decode(deptno,40,sal)) dept40
            from scott.emp
            group by job)
            order by 1;
            • 3. Re: Column to Row Display
              Nimish Garg
              Pivot is used to convert Rows to columns.

              I think u need to use union as
              select col1 from table
              select col2 from table
              select col252 from table

              Nimish Garg
              • 4. Re: Column to Row Display
                Union possibly can be done for lesser number of columns.
                but, i have 252 columns where in approx I need nearly 60 columns to be converted to rows.

                this is very huge to write using union.

                can you pls mention any other option instead of this a we would have too many lines of code in a single select statement.
                • 5. Re: Column to Row Display
                  checked links given by you. links shows examples with 2 or 3 columns, but I am referring to nearly 60 columns.
                  Will that not be performace problem if i use same method in my issue.

                  pls suggest, if possible, any other method where in i can work out for more columns.
                  • 6. Re: Column to Row Display
                    Will that not be performace problem if i use same method in my issue.
                    It depends on how many records you're querying and how often.
                    You want to turn 60 columns into 60 rows?
                    Should not be a problem.
                    You could load those records into a new table and let your report query on that new table.
                    • 7. Re: Column to Row Display
                      Frank Kulash

                      Always post your Oracle version. Don't just add an easy-to-miss tag like "10G" to your message; say something clear, like "I'm using Oracle".

                      This is called Unpivot . One way to do it is to cross-join your table to a Counter Table , any table (or sub-query, as in the example below) that has as many rows as you need in your output.
                      To unpivot all 252 columns:
                      WITH     cntr     AS
                           SELECT     LEVEL     AS n
                           FROM     dual
                           CONNECT BY     LEVEL     <= 252
                      SELECT     c.n
                      ,     CASE
                               WHEN  c.n <= 100
                               THEN  CASE c.n
                                     WHEN   1  THEN     x.col_1
                                     WHEN   2  THEN  x.col_2
                                     WHEN 100  THEN  x.col_100
                               WHEN  c.n <= 200
                               THEN  CASE c.n
                                     WHEN 101  THEN     x.col_101
                                     WHEN 102  THEN  x.col_102
                                     WHEN 200  THEN  x.col_200
                               WHEN  c.n <= 300
                               THEN  CASE c.n
                                     WHEN 201  THEN     x.col_201
                                     WHEN 202  THEN  x.col_202
                                     WHEN 252  THEN  x.col_252
                           END     AS data_col
                      FROM          cntr     c
                      CROSS JOIN     table_x     x
                      The nested CASE expressions are necessary because you can't have more than 127 WHEN-THEN pairs in a single CASE expression.
                      If you're choosing 60 columns out of the 252 to unoivot, then you only need one CASE expression.