7 Replies Latest reply: Mar 16, 2006 2:38 PM by Kevin_K RSS

    Transform columns to rows

    zd*417836*ka
      Hi, I would like transform columns to rows.
      i.e. source table:

      colA colB colC colD colE colX
      aa bb cc dd ee 1
      ff gg hh ii jj 2

      ..and output table-solution:
      colText colValue colX
      colA aa 1
      colB bb 1
      colC cc 1
      colD dd 1
      colE ee 1
      colA ff 2
      colB gg 2
      colC hh 2
      colD ii 2
      colE jj 2

      It's possible do it only with select - it would be the best solution.
      Thanks zdenek T.
        • 1. Re:  Transform columns to rows
          422895
          SELECT 'colA', colA, colX
          FROM table_name
          UNION ALL
          SELECT 'colB', colB, colX
          FROM table_name
          UNION ALL
          SELECT 'colC', colC, colX
          FROM table_name
          UNION ALL
          SELECT 'colD', colD, colX
          FROM table_name
          UNION ALL
          SELECT 'colE', colE, colX
          FROM table_name
          • 2. Re:  Transform columns to rows
            421128
            Here is another type of solution, which performs better for larger tables.

            Note that the WITH subquery generates a single column table containing 1-5.
            with nums as (
            select rownum rn from (select 1,2,3 from dual group by cube (1, 2, 3)) where rownum <= 5)
            
            select decode(rn, 1, 'colA', 2, 'colB', 3, 'colC', 4, 'colD', 5, 'colE') "colText", 
                   decode(rn, 1, colA, 2, colB, 3, colC, 4, colD, 5, colE) "colValue",
                   colX
            from table1, nums
            • 3. Re:  Transform columns to rows
              William Robertson
              You could also do it with a couple of types:
              CREATE OR REPLACE TYPE name_charvalue_ot AS OBJECT (name VARCHAR2(30), value VARCHAR2(4000))    
              /

              CREATE OR REPLACE TYPE name_charvalue_tt AS TABLE OF name_charvalue_ot
              /

              SELECT arr.name, arr.value, t.colx
              FROM   testit t
                   , TABLE
                     ( NAME_CHARVALUE_TT
                       ( NAME_CHARVALUE_OT('Col A', t.cola)
                       , NAME_CHARVALUE_OT('Col B', t.colb)
                       , NAME_CHARVALUE_OT('Col C', t.colc)
                       , NAME_CHARVALUE_OT('Col D', t.cold)
                       , NAME_CHARVALUE_OT('Col E', t.cole) ) ) arr;

              NAME                           VALUE            COLX
              ------------------------------ ---------- ----------
              Col A                          aa                  1
              Col B                          bb                  1
              Col C                          cc                  1
              Col D                          dd                  1
              Col E                          ee                  1
              Col A                          ff                  2
              Col B                          gg                  2
              Col C                          hh                  2
              Col D                          ii                  2
              Col E                          jj                  2

              10 rows selected.
              • 4. Re:  Transform columns to rows
                358102
                Here is another way.
                SQL> DESC MYTABLE
                 Name                                      Null?    Type
                 ----------------------------------------- -------- ---------------
                 COLA                                               VARCHAR2(20)
                 COLB                                               VARCHAR2(20)
                 COLC                                               VARCHAR2(20)
                 COLD                                               VARCHAR2(20)
                 COLE                                               VARCHAR2(20)
                 COLX                                               VARCHAR2(20)
                
                SQL> SELECT * FROM MYTABLE;
                
                COLA                 COLB                 COLC
                -------------------- -------------------- --------------------
                COLD                 COLE                 COLX
                -------------------- -------------------- --------------------
                aa                   bb                   cc
                dd                   ee                   1
                
                ff                   gg                   hh
                ii                   jj                   2
                
                
                
                  1  SELECT COLUMN_NAME,DECODE(COLUMN_ID,1,COLA,2,COLB,3,COLC,4,COLD,5,COLE),COLX
                  2  FROM MYTABLE M,USER_TAB_COLUMNS U
                  3  WHERE TABLE_NAME = 'MYTABLE'
                  4  AND COLUMN_NAME <> 'COLX'
                  5* ORDER BY 2,3
                SQL> /
                
                COLUMN_NAME                    DECODE(COLUMN_ID,1,C COLX
                ------------------------------ -------------------- --------------------
                COLA                           aa                   1
                COLB                           bb                   1
                COLC                           cc                   1
                COLD                           dd                   1
                COLE                           ee                   1
                COLA                           ff                   2
                COLB                           gg                   2
                COLC                           hh                   2
                COLD                           ii                   2
                COLE                           jj                   2
                
                10 rows selected.
                • 5. Re:  Transform columns to rows
                  421128
                  Timings:
                  select count(*) from
                  (SELECT 'colA', colA, colX
                  FROM table1
                  UNION ALL
                  SELECT 'colB', colB, colX
                  FROM table1
                  UNION ALL
                  SELECT 'colC', colC, colX
                  FROM table1
                  UNION ALL
                  SELECT 'colD', colD, colX
                  FROM table1
                  UNION ALL
                  SELECT 'colE', colE, colX
                  FROM table1);
                  
                    COUNT(*)
                  ----------
                     1616820
                  
                  Executed in 0.203 seconds
                  
                  with nums as (
                  select rownum rn from (select 1,2,3 from dual group by cube (1, 2, 3)) where rownum <= 5)
                  select count(*) from
                  (select decode(rn, 1, 'colA', 2, 'colB', 3, 'colC', 4, 'colD', 5, 'colE') "colText",
                         decode(rn, 1, colA, 2, colB, 3, colC, 4, colD, 5, colE) "colValue",
                         colX
                  from table1, nums);
                  
                    COUNT(*)
                  ----------
                     1616820
                  
                  Executed in 0.156 seconds
                  
                  select count(*) FROM
                  (SELECT arr.name,
                         arr.value,
                         t.colx
                    FROM table1 t,
                         TABLE (NAME_CHARVALUE_TT (NAME_CHARVALUE_OT('Col A', t.cola),
                                                   NAME_CHARVALUE_OT('Col B', t.colb),
                                                   NAME_CHARVALUE_OT('Col C', t.colc),
                                                   NAME_CHARVALUE_OT('Col D', t.cold),
                                                   NAME_CHARVALUE_OT('Col E', t.cole) ) ) arr) ;
                  
                    COUNT(*)
                  ----------
                     1616820
                  
                  Executed in 2.562 seconds
                  
                  SELECT COUNT(*) FROM
                  (SELECT COLUMN_NAME,DECODE(COLUMN_ID,1,COLA,2,COLB,3,COLC,4,COLD,5,COLE),COLX
                     FROM TABLE1 M,USER_TAB_COLUMNS U
                     WHERE TABLE_NAME = 'TABLE1'
                     AND COLUMN_NAME <> 'COLX');
                  
                    COUNT(*)
                  ----------
                     1616820
                  
                  Executed in 30.25 seconds
                  • 6. Re:  Transform columns to rows
                    358102
                    Timings on my system:
                    Alex's query.
                    
                    SQL> r
                      1  with nums as (
                      2  select rownum rn from (select 1,2,3 from dual group by cube (1, 2, 3)) where rownum 
                      3  select count(*) from
                      4  (select decode(rn, 1, 'colA', 2, 'colB', 3, 'colC', 4, 'colD', 5, 'colE') "colText",
                      5         decode(rn, 1, colA, 2, colB, 3, colC, 4, colD, 5, colE) "colValue",
                      6         colX
                      7* from mytable, nums)
                    
                      COUNT(*)
                    ----------
                       1616820
                    
                    Elapsed: 00:00:01.12
                    SQL> r
                      1  with nums as (
                      2  select rownum rn from (select 1,2,3 from dual group by cube (1, 2, 3)) where rownum 
                      3  select count(*) from
                      4  (select decode(rn, 1, 'colA', 2, 'colB', 3, 'colC', 4, 'colD', 5, 'colE') "colText",
                      5         decode(rn, 1, colA, 2, colB, 3, colC, 4, colD, 5, colE) "colValue",
                      6         colX
                      7* from mytable, nums)
                    
                      COUNT(*)
                    ----------
                       1616820
                    
                    Elapsed: 00:00:01.19
                    
                    Vyacheslav's query.
                    
                    SQL> select count(*) from
                      2  (SELECT 'colA', colA, colX
                      3  FROM mytable
                      4  UNION ALL
                      5  SELECT 'colB', colB, colX
                      6  FROM mytable
                      7  UNION ALL
                      8  SELECT 'colC', colC, colX
                      9  FROM mytable
                     10  UNION ALL
                     11  SELECT 'colD', colD, colX
                     12  FROM mytable
                     13  UNION ALL
                     14  SELECT 'colE', colE, colX
                     15  FROM mytable);
                    
                      COUNT(*)
                    ----------
                       1616820
                    
                    Elapsed: 00:00:00.66
                    SQL> r
                      1  select count(*) from
                      2  (SELECT 'colA', colA, colX
                      3  FROM mytable
                      4  UNION ALL
                      5  SELECT 'colB', colB, colX
                      6  FROM mytable
                      7  UNION ALL
                      8  SELECT 'colC', colC, colX
                      9  FROM mytable
                     10  UNION ALL
                     11  SELECT 'colD', colD, colX
                     12  FROM mytable
                     13  UNION ALL
                     14  SELECT 'colE', colE, colX
                     15* FROM mytable)
                    
                      COUNT(*)
                    ----------
                       1616820
                    
                    Elapsed: 00:00:00.65
                    
                    My query.
                    
                    SQL> ED
                    Wrote file afiedt.buf
                    
                      1  SELECT COUNT(*) FROM
                      2  (SELECT COLUMN_NAME,DECODE(COLUMN_ID,1,COLA,2,COLB,3,COLC,4,COLD,5,COLE),COLX
                      3     FROM MYTABLE M,USER_TAB_COLUMNS U
                      4     WHERE TABLE_NAME = 'MYTABLE'
                      5*    AND COLUMN_NAME <> 'COLX')
                    SQL> /
                    
                           COUNT(*)
                    ---------------
                            1616820
                    
                    Elapsed: 00:02:26.32
                    • 7. Re:  Transform columns to rows
                      Kevin_K
                      I need to compare two rows and show column values side by side from 2 rows:

                      For example:

                      mytable
                      ID col1 col2 col3 col4
                      1 Y N Y Y
                      2 Y Y N Y

                      I need results like this:

                      col_row_id1 col_row_id2
                      Y Y
                      N Y
                      Y N
                      Y Y


                      Can someone please tell me how to do this?

                      Thanks!