Concatenate multiple columns into one columns with comma sep

[sandeepforever007]

Hi ,

We have a table in the following format .

Code: Select all

EMPNO  EMPNAME   IND1  IND2   IND3  SALARY
1        x        y    n       y     500
2        y        n    y       n     501
3        z        n    n       y     302
4        p        n    n       n     210


Desired OUTPUT  : 


1,x,"IND1,IND3",500
2,Y,"IND2",501
3,Z,"IND3",302
4,P,"",210

     

COnditions :

1. IF IND is Y , then we have to display the column name
2. IF IND is N , We simply needs to skip .
COuld you please help us in getting the desired output throug Sql statement .


With regards
Sandeep

[dhiraj]

Why don't you use simple fetch and use if else in program?
or you can try for CASE during SELECT.


Thanks,
--Dhiraj

[NicC]

select col1, ",", col2, ",",Case ind1 when 'y' then 'IND1' else "" End....etc

[sandeepforever007]

HI Nic,
I have tried out the way you mentioned.
there is a problem with the comma.

Code: Select all

SELECT col1,col2,
'"'||
CASE IND1 WHEN 'Y' THEN 'IND1,'
                  WHEN 'N' THEN '' 
END
CONCAT
CASE IND2 WHEN 'Y' THEN 'IND2,'
                  WHEN 'N' THEN ''
END
||'"', col4

when IND1 is Y and IND2 is N then i am getting a comma after IND1 which shouldnt come.

Code: Select all

OUTPUT:
1,x,"IND1,"500

Could you please help me out

Thanks
Sandeep

[NicC]

when IND1 is Y and IND2 is N then i am getting a comma after IND1 which shouldnt come.

because you are coding the comma as part of the WHEN IND1 = 'Y'.

I am by no means an expert on SQL. I would have to create a table and experiment but you can do that. If you want to play at home then download SQLite, or similar, to your own computer and experiment. Or you could wait until someone who knows comes along.

[NicC]

To get what you want for your indicators:

Code: Select all

CASE col3||col4||col5
   WHEN 'yyy' THEN '"IND1,IND2,IND3"'
   WHEN 'yyn' THEN '"IND1,IND2"'
   WHEN 'ynn' THEN '"IND1"'
   WHEN 'yny' THEN '"IND1,IND3"'
    etc until all combinations covered
END

Note: || is the concatenation symbol in SQLite

[sandeepforever007]

Hi Nic,

We can not go with the suggested solution , Because we are having 11 Indicator variables hence writing combinations is very difficult .

As this req is very much needed for us . Could you pls suggest an appropriate Query for the same .

With Regards
Sandeep

[NicC]

Why not let DB2 do what it does best - get the data for you? Then let the program that issued the SELECT do what the program does best - manipulate data. Return all 11 indicators and have the program analyse them and create the last bit of your puzzle.