CODEWITHSUNDEEP
oracle-databaseoracle11g
user
user

Reputation: 1

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

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

Upvotes: 0

Views: 24

Answers (2)

Mahamoutou
Mahamoutou

Reputation: 1555

You can also use UNPIVOT clause with INCLUDE NULLS option like below.

with sample_data (ColA, ColB, ColC ,ColD ,ColE) as (
select 1, 'abc', 'def', 'ghi', 'jkl' from dual union all
select 2, 'mbc', 'nef', 'ohi', 'pkl' from dual union all
select 3, 'abc', ''   , 'grt', 'qkt' from dual union all
select 4, 'alc', 'dhj', 'li' , 'ttl' from dual union all
select 5, 'rec', 'dtf', 'goi', 'jdr' from dual
)  
select cola||';'||col_name||';'||col_value
from sample_data 
unpivot include nulls(
col_value for col_name in (
    colb as 'colB'
,   colc as 'colC'
,   cold as 'colD'
,   cole as 'colE'
)
)
;

db<>fiddle

Upvotes: 0

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

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

Upvotes: 0

Related Questions