Search code examples
sqloracle-databaseoracle11gunpivot

Transform column to row data in Oracle

Eg : #Oracle

Sample data :

ID A_ID B_ID C_ID D_ID
1 'ABC' 'XYZ' 'PLO' 'QWE'

Expected Output :

ID A_ID
1 'ABC'
1 'XYZ'
1 'PLO'
1 'QWE'
Solution

  • You can use the unpivot clause:

    select id, a_id
from your_table
unpivot (a_id for orig_id in (a_id, b_id, c_id, d_id))
    ID A_ID
    1 ABC
    1 XYZ
    1 PLO
    1 QWE

    fiddle

    The operation creates an extra column, which I've called orig_id, which can be used to identify which of the original columns a given value came from. I've excluded it from the select list, but the fiddle includes a version that shows it. You can also change the value that appears, by adding as '...' to each column name in the in () clause.

    You can read more about pivoting and unpivoting in this article.