CODEWITHSUNDEEP
sqlsql-servercase
joe
joe

Reputation: 1473

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

Upvotes: 1

Views: 3210

Answers (3)

AVINASH KUMAR
AVINASH KUMAR

Reputation: 1

CASE Statement with Syntex and example

Syntex CASE expression

 WHEN value_1 THEN result_1

   WHEN value_2 THEN result_2

   ...
WHEN value_n THEN result_n



 ELSE result

END

OR

CASE
WHEN condition_1 THEN result_1
   WHEN condition_2 THEN result_2
   ...
WHEN condition_n THEN result_n
   ELSE result
END

Example

    SELECT contact_id,
CASE website_id
WHEN 1 THEN 'TechOnTheNet.com'
  WHEN 2 THEN 'CheckYourMath.com'
  ELSE 'BigActivities.com'
END
FROM contacts;

Or you could write the SQL statement using the CASE statement like this CASE

    WHEN website_id = 1 THEN 'TechOnTheNet.com'

  WHEN website_id = 2 THEN 'CheckYourMath.com'

  ELSE 'BigActivities.com'

END

FROM contacts;

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39537

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;

Upvotes: 4

JamieD77
JamieD77

Reputation: 13949

This is also referred to as a PIVOT statement

SELECT   FY, LAT, LON, ID, MEX, USA
FROM     TEMP t
PIVOT (
    MAX([CHECK])
    FOR [COUNTRY] IN([MEX],[USA])
) p

Upvotes: 2

Related Questions