How to create a summary query in SQL Server with a CASE expression?

Question

Col1 contains only X and Y.

Col1 Col2 X abc Y pqr X pqr X mnq Y cxr 

I want to do it as follows:

 XY Col2 Yes Yes pqr Yes No abc Yes No mnq No Yes cxr 

What SQL query should I write?

Answer 1

Solution using SQL PIVOT operator :

 SELECT Col2, case when X=0 then 'No' else 'Yes' end as X, case when Y=0 then 'No' else 'Yes' end as Y FROM MyTable PIVOT ( count(Col1) FOR Col1 IN ([X], [Y]) ) AS PivotTable; 

Sample launch: http://www.sqlfiddle.com/#!3/5856d/14

Answer 2

try the following:

 with cte as (select col2, min(col1)as X, min(col1) as Y, count(distinct col1) as cnt from your_table group by col2) select COL2, case when X='X' then 'Yes' else 'No' end X, case when Y='Y' OR cnt=2 then 'Yes' else 'No' end Y from cte 

SQL Fiddle Demo

Answer 3

try the following:

 select col2,CASE WHEN COUNT(*)=1 then CASE WHEN min(col1)='X' then 'YES' else 'NO' end else 'YES' end as 'X', CASE WHEN COUNT(*)=1 then CASE WHEN min(col1)='Y' then 'YES' else 'NO' end else 'YES' end as 'Y' from MyTable group by col2