query to produce multiple column into a single row

Analytics
New Member

query to produce multiple column into a single row

Hi All,

 

I need to write a query to produce a heat map in excel sheet . let me give an example :

 

element_name           source1     source2    source 3

element1                        x                 x

element2                                           x              x    

 

how do we write a query to produce the below output :

 

element_name       sourcename

element1                  source1,source2

element2                  source2,source3

 

Any kind of help is greatly    appreciated. Please can anyone guide me. I have been trying really hard to write a query to produce the desired result but unable to.

 

Thanks,

Divya

1 REPLY
Enthusiast

Re: query to produce multiple column into a single row

Below should work.

 

SEL ELEMENT_NAME, oreplace(SOURCENAME,',,','')
FROM( SEL ELEMENT_NAME, CASE WHEN SOURCE1 IS NULL AND SOURCE2 IS NULL AND SOURCE3 IS NULL THEN NULL ELSE
COALESCE(SOURCE1,',')||','||COALESCE(SOURCE2,',')||','||COALESCE(SOURCE3,',') END AS SOURCENAME FROM TABLE )A;