data in single row

[mrcool]

Hi All,

I have a requirement for a simple list report as below:

Project     DataModeling ETL     Cognos

Proj1          In progress (NULL)       (Null)
Proj2          Completed  In progress  (Null)
Proj3          Completed  Completed      In Progress

and so on

For the above requirement I am deriving the columns " Data Modeling","ETL","Cognos" using a case statement

case when abc=dataModeling then Status(Status is a column which has values In progress, completed etc)
END
I have 3 case statements like above. by doing so I am getting the report output format as below.


Project     DataModeling ETL        Cognos

Proj1          In progress (NULL)        (Null)
Proj2          Completed 
                                 In progress 
                                                 (Null)
Proj3          Completed 
                                 Completed 
                                                 In Progress

By applying group by on all the columns I am getting output as stated above. Could you please help if we can achieve this in single row using single query.
Other option I could think of is to use separate query for each column and join with main query.
Thanks in advance.

Cheers,
Mrcool

[blom0344]

Wrap minimum/maximum around the case expression. This is the standard way to create 'buckets' of data into a single row:

Code Select Expand


select Id as ID, max(case  when CAST( Someotherid AS VARCHAR( 6 ) ) = '360451' then '360451' else NULL end ) AS Id1, max(case  when CAST( Someotherid AS VARCHAR( 6 ) ) = '360453' then '360453' else NULL end ) AS Id2, max(case  when CAST( Someotherid AS VARCHAR( 6 ) ) = '360455' then '360455' else NULL end ) AS Id3 group by Id


[mrcool]

Thank You Blom.. :) I will try this..

Cheers,
Mrcool