Wednesday, October 21, 2009

A unpivot example (three rows into X rows ) with SQL

I have the following information :

OOO 1 2 3
--- - - -
 A  X X
 B  X   X
 C    X X

And I want this as output:

000   print
---   -----
 A      1
 A      2
 B      1
 B      3
 C      2
 C      3

 This can be done with the following SQL statement:

select * from 
   (select OOO,
               case
                     when PR_type.rowno = 1 then decode("1",'X','1',NULL)
                     when PR_type.rowno = 2 then decode("2",'X','2',NULL)
                     when PR_type.rowno = 3 then decode("3",'X','3',NULL)
               end print
    from T
            , (select     rownum rowno
                 from     dual
                 connect by level <= 3) pr_type) a_pr
where a_pr.print is not null
order by 1,2
/

OOO P
--- -
 A  1
 A  2
 B  1
 B  3
 C  2
 C  3

6 rijen zijn geselecteerd.

SQL> select * from t;

OOO 1 2 3
--- - - -
 A  X X
 B  X   X
 C    X X