SQL Server pivot using case statement

Question

I'm trying to pivot out some data and I think I need to incorporate a case statement in my pivot code but I'm not sure how. I have the table below:

ID      AreaCode
1001    1501
1001    1502
1001    2301
1031    1010
1031    3012
1048    2304
1048    3012
1048    4022

The first digit of each AreaCode refers to a body area, I'm using the code below to indicate which body area is affected:

select id,
case when left(areaID,1)=1 then 'Yes' end Head,
case when left(areaID,1)=2 then 'Yes' end Face,
case when left(areaID,1)=3 then 'Yes' end Neck,
case when left(areaID,1)=4 then 'Yes' end Abdo
from #testcase

Which gives me the following:

id      Head    Face    Neck    Abdo
1001    Yes     NULL    NULL    NULL
1001    Yes     NULL    NULL    NULL
1001    NULL    Yes     NULL    NULL
1031    Yes     NULL    NULL    NULL
1031    NULL    NULL    Yes     NULL
1048    NULL    Yes     NULL    NULL
1048    NULL    NULL    Yes     NULL
1048    NULL    NULL    NULL    Yes

However, I need my output table to contain one row for each id, like so:

id      Head    Face    Neck    Abdo
1001    Yes     Yes     Null    Null
1031    Yes     Null    Yes     Null
1048    Null    Yes     Yes     Yes

Can incorporate my case statement in a pivot to achieve this? Thanks

Answer 1

You need to use aggregate on top of case statements

SELECT id,
       Max(CASE
             WHEN LEFT(areaID, 1) = 1 THEN 'Yes'
           END) Head,
       Max(CASE
             WHEN LEFT(areaID, 1) = 2 THEN 'Yes'
           END) Face,
       Max(CASE
             WHEN LEFT(areaID, 1) = 3 THEN 'Yes'
           END) Neck,
       Max(CASE
             WHEN LEFT(areaID, 1) = 4 THEN 'Yes'
           END) Abdo
FROM   #testcase
GROUP  BY id 

Answer 2

You could use a real PIVOT solution instead like this:

DECLARE @t table(ID int, AreaCode int)
INSERT @t 
VALUES
  (1001,1501),(1001,1502),(1001,2301),(1031,1010),
  (1031,3012),(1048,2304),(1048,3012),(1048,4022)

SELECT id, [1]Head, [2]Face, [3]Neck, [4]Abdo
FROM  
( 
  SELECT id, left(AreaCode, 1) Area, 'Yes' x
  FROM @t
) p 
PIVOT (Max(x) FOR [Area] IN ([1],[2],[3],[4])) AS pvt 

Result:

id    Head  Face  Neck  Abdo
1001  Yes   Yes   NULL  NULL
1031  Yes   NULL  Yes   NULL
1048  NULL  Yes   Yes   Yes