Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to create a pivot query in SQL Server with CASE Statement?

Tags:

sql

sql-server

Col1 contains only X and Y.

Col1    Col2

X       abc

Y       pqr

X       pqr

X       mnq

Y       cxr

I want to make it like this:

X    Y    Col2

Yes  Yes  pqr
Yes  No   abc
Yes  No   mnq
No   Yes  cxr

What SQL Query i should write?

like image 458
Yogesh Avatar asked Oct 04 '12 09:10

Yogesh


2 Answers

Solution using the 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;

Running sample: http://www.sqlfiddle.com/#!3/5856d/14

like image 168
Cristian Lupascu Avatar answered Oct 20 '22 03:10

Cristian Lupascu


try this:

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

like image 42
Joe G Joseph Avatar answered Oct 20 '22 02:10

Joe G Joseph