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
Wrap minimum/maximum around the case expression. This is the standard way to create 'buckets' of data into a single row:
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
Thank You Blom.. :) I will try this..
Cheers,
Mrcool