Convert column names as row values in oracle

1
votes

I have bumped into a problem, where I want to convert column names of a table as row values and combine these column names with the data of that table to which these column belongs.

I have data table as follows:

ID|QUERY_REF|COL_V1|COL_V2|COL_V3|COL_V4|COL_V5|COL_V6
1|Q127|hdaskjdhakdjsadj asudakjdakhdkjjdjkskjdjsakhasuygwyeqe1747434834094432uilknfja|||||

Desired output

ID|QUERY_REF|COLUMNS
1|Q127|COL_V1
1|Q127|COL_V2
1|Q127|COL_V3
1|Q127|COL_V4
1|Q127|COL_V5
1|Q127|COL_V6

So far I have searched web, but no example corresponds to my problem.

I have developed a select statement using union. But it does not gives me my desired result. Please help.

1
oracle11g

1 Answers

1
votes

Assuming that you only want the column names for which there is at least one value in the column, you can use unpivot for that:

select distinct ID, QUERY_REF, COLUMNS 
from tableName
UNPIVOT (cols FOR COLUMNS IN (COL_V1 AS 'COL_V1', COL_V2 AS 'COL_V2', COL_V3 AS 'COL_V3', COL_V4 AS 'COL_V4', COL_V5 AS 'COL_V5', COL_V6 AS 'COL_V6'));