Concatenate

General

Concatenate

  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.

Example

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

5 REPLIES
Enthusiast

Re: Concatenate

Hi,

You want to populate the column names OR column values?

Khurram

Re: Concatenate

Hi,

I need to populate Column names that has the values with 1

Re: Concatenate

Hi,
Some how I have the got result using the DECODE function

Sel DECODE(COL1,1,'COL1)
|| DECODE(COL2,1,'COL2)
|| DECODE(COL3,1,'COL3)
|| DECODE(COL4,1,'COL4)

But now I need to add comma between the two columns

Sel DECODE(COL1,1,'COL1) || ','
|| DECODE(COL2,1,'COL2) || ','
|| DECODE(COL3,1,'COL3) || ','
|| DECODE(COL4,1,'COL4) || ','

If I give like the above, it will populate as COL1,,COL3 when COL2 does not have the value as 1. I need to populated it as COL1,COL3

Please help me in how to avoid this

Re: Concatenate

Hi All,

Finally I made it to simple 

Sel DECODE(COL1,1,'COL1,')

|| DECODE(COL2,1,'COL2,')

|| DECODE(COL3,1,'COL3,')

|| DECODE(COL4,1,'COL4,') as Column_name

,Trim(Trailing ',' from Column_name) 

From Table

Thanks for the people who made a time read it 

Fan

Re: Concatenate

Hi, 

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;

Result set

COL1,COL5

COL2,COL4

COL1,COL2,COL3,COL4,COL5