oracle11g - I need to convert oracle table data that I get 1 column value fixed as identifier and all other column name & values as a single row

Question

I have a oracle table with multiple columns, I need to convert data in such a way that 1 column value is fixed and other column names and their values displays as a single row.

for ex- Table is currently like this :

|ColA| ColB| ColC |ColD |ColE|

1      abc  def   ghi  jkl

2      mbc  nef   ohi  pkl

3      abc        grt  qkt

4      alc  dhj   li   ttl

5      rec  dtf   goi  jdr

The Final format in which I need data is :

1;colB;abc

1;ColC;def

1;colD;ghi

1;colE;jkl

2;colB;mbc

2;ColC;nef

2;colD;ohi

2;colE;pkl

question from:https://stackoverflow.com/questions/65936950/i-need-to-convert-oracle-table-data-that-i-get-1-column-value-fixed-as-identifie

Answer 1

This should take care of it for you:

SELECT *
  FROM (SELECT COLA || ';' || 'ColB;' || COLB AS DATA FROM SOME_TABLE UNION ALL
        SELECT COLA || ';' || 'ColC;' || COLC FROM SOME_TABLE UNION ALL
        SELECT COLA || ';' || 'ColD;' || COLD FROM SOME_TABLE UNION ALL
        SELECT COLA || ';' || 'ColE;' || COLE FROM SOME_TABLE)
  ORDER BY DATA

db<>fiddle here