I am having 5 columns say Col1 to Col5. The data present in Col1 to Col 5 is either 0 or 1.
My requirement is I need to populate the data in a FLG column as wherever the data as 1.
If suppose my first row has values in Col1,Col3 and Col5.
FLG column should populate as Col1,Col3,Col5.
Col1 Col2 Col3 Col4 Col5 FLG
1 0 1 0 1 Col1,Col3,Col5
0 1 0 1 0 Col2,Col4
0 0 0 0 1 Col5
1 1 1 1 1 Col1,Col2,Col3,Col4,Col5
Finally I made it to simple
|| DECODE(COL4,1,'COL4,') as Column_name
,Trim(Trailing ',' from Column_name)
Thanks for the people who made a time read it
I can see your solution. But just thought of sharing below logic.
CREATE MULTISET TABLE TBL(COL1 SMALLINT,COL2 SMALLINT,COL3 SMALLINT,COL4 SMALLINT,COL5 SMALLINT);
INSERT INTO TBL(1,0,0,0,1);
INSERT INTO TBL(0,1,0,1,0);
INSERT INTO TBL(1,1,1,1,1);
INSERT INTO TBL(0,0,0,0,0);
SELECT TRIM( CASE WHEN COL1=0 THEN '' ELSE 'COL1' END) || CASE WHEN COL2=0 THEN '' ELSE ',COL2' END
|| CASE WHEN COL3=0 THEN '' ELSE ',COL3' END
|| CASE WHEN COL4=0 THEN '' ELSE ',COL4' END || TRIM(CASE WHEN COL5=0 THEN '' ELSE ',COL5' END ) AS COLUMNNAME, TRIM (BOTH ',' FROM COLUMNNAME) FROM TBL;