joe joe - 4 years ago 81
SQL Question

How to use CASE statement in SQL Server

I am trying to use the

CASE
statement in SQL Server to solve an issue I have.

This is my sample data:

FY COUNTRY LAT LON CHECK ID
------------------------------------------------------------
2013 MEX 35.85311 -118.1385 Y 80845
2013 USA 35.85311 -118.1385 Y 80845


Here is my
CASE
statement in SQL Server

SELECT
FY,
LAT, LON,
CASE
WHEN COUNTRY = 'MEX'
THEN 'Y'
END AS MEX,
CASE
WHEN COUNTRY = 'USA'
THEN 'Y'
END AS USA,
ID
FROM
TEMP


My result:

FY LAT LON MEX USA ID
---------------------------------------------------
2013 35.85311 -118.1385 Y (null) 80845
2013 35.85311 -118.1385(null) Y 80845


Is there a way to convert my result to:

FY LAT LON MEX USA ID
--------------------------------------------------------
2013 35.85311 -118.1385 Y Y 80845

Answer Source

Using group by and max:

select
    fy, lat, lon, 
    max(case when country = 'MEX' then 'Y' end) as mex, 
    max(case when country = 'USA' then 'Y' end) as usa, 
    id
from temp
group by
fy, lat, lon, id;

or may be using PIVOT:

select fy, lat, lon, id, mex, usa
from temp t
pivot ( max(check) for country in ([mex],[usa]) ) p;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download