0

much like this original SO Pivoting rows into columns dynamically in Oracle

but i would like to do the opposite

how can i get 

 ID       NAME    AGE    GENDER  STATUS
----     -----   -----  ------  --------
  1      Bob      30     male 
  2      Susan                   married

into this

ID       K       V
----     -----   -----
  1      name    Bob
  1      age     30
  1      gender  male
  2      name    Susan
  2      status  married
Community
  • 1
  • 1
Jay Rizzi
  • 4,196
  • 5
  • 42
  • 71

1 Answers1

2

You are looking for unpivot.

select * from t 
unpivot (
          v for k in ("NAME","AGE","GENDER","STATUS")
        ) u

You may have a type mismatch if the age column is an integer. In that case convert it to a character before unpivoting.

select *
from (select id,name,to_char(age) age,gender,status from t) t 
unpivot (
         v for k in ("NAME","AGE","GENDER","STATUS")
        ) u
Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58