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?
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;
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With