Need to transform the rows into columns for the similar ID's in oracle

Question

Hi,

I need to transform the rows into columns for the similar ID's in oracle

e.g. The following is the result I will get if i query my database

Col1    Col2      Col3 
----    ----      ----
1        ABC       Yes
1        XYZ       NO
2        ABC       NO

I need to transform this into

Col1    Col2    Col3       Col4        Col5
----    ----    ----       ----        ----
1        ABC     Yes        XYZ          No
2        ABC     NO        NULL        NULL

Someone please help me in solving this issue

Thanks, Siv

Answer 1

Based on AskTom:

select Col1,
max( decode( rn, 1, Col2 ) ) Col_1,
max( decode( rn, 1, Col3 ) ) Col_2,
max( decode( rn, 2, Col2 ) ) Col_3,
max( decode( rn, 2, Col3 ) ) Col_4
from (
    select Col1,
           Col2,
           Col3,
           row_number() over (partition by Col1 order by Col2 desc nulls last) rn
       from MyTable
            )
group by Col1;

I don't have access to an Oracle db to test it but I think that will work. If there could be more than two records per ID then you could just add more rows to the select cause with the corresponding row number.

Answer 2

One solution is to use the 10g MODEL clause:

SQL> select col1
  2         , col2
  3         , col3
  4         , col4
  5         , col5
  6  from   t23
  7  model
  8    return updated rows
  9    partition by ( col1 )
 10    dimension by ( row_number() over ( partition by col1
 11                                       order by col2 desc nulls last) rnk
 12                 )
 13    measures (col2, col3, lpad(' ',4) col4, lpad(' ',4) col5)
 14    rules upsert
 15    (
 16      col2 [0] = col2 [1]
 17      , col3 [0] = col3 [1]
 18      , col4 [0] = col2 [2]
 19      , col5 [0] = col3 [2]
 20    )
 21  /

      COL1 COL2 COL3 COL4 COL5
---------- ---- ---- ---- ----
         1 ABC  Yes  ABC  NO
         2 XYZ  NO

SQL>

It is an unfortunate truth about such solutions that we need to specify the number of columns in the query. That is, in regular SQL there is no mechanism for determining that the table contains three rows where COL1 = 1 so we need seven columns, which is not unreasonable. For situations in which the number of pivot values is unknown at the time of coding there is always dynamic sql.