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?
-
2I think this can be done using a PIVOT query: msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspxCristian Lupascu– Cristian Lupascu2012-10-04 09:07:41 +00:00Commented Oct 4, 2012 at 9:07
Add a comment
|
3 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
1 Comment
Cristian Lupascu
I've simplified the query by eliminating the sub-select
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
Comments
try this:
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
2 Comments
Bob Goblin
How does this show how to create a pivot? This shows how to use a case statement.
Reversed Engineer
@user2676140, a pivot can be created using CASE statements. If you don't believe me, please see above! No reason to downvote this. No reason to upvote it either, since the built in PIVOT can do the job, although sometimes CASE statements work better.