title: SQL Server pivot using case statement
body: <p>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:</p>
  
  <pre><code>ID      AreaCode
  1001    1501
  1001    1502
  1001    2301
  1031    1010
  1031    3012
  1048    2304
  1048    3012
  1048    4022
  </code></pre>
  
  <p>The first digit of each AreaCode refers to a body area, I'm using the code below to indicate which body area is affected:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Which gives me the following:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>However, I need my output table to contain one row for each id, like so:</p>
  
  <pre><code>id      Head    Face    Neck    Abdo
  1001    Yes     Yes     Null    Null
  1031    Yes     Null    Yes     Null
  1048    Null    Yes     Yes     Yes
  </code></pre>
  
  <p>Can incorporate my case statement in a pivot to achieve this? Thanks</p>