title: Pivot vs Case T-sql efficiency
body: <p>I am trying to figure out which way is more efficient.  I tried to use Sql Fiddle but it didn't seem to recognize PIVOT or UNION
  What tools or statistics can I look at to determine which is more efficient.</p>
  
  <pre><code>CREATE TABLE T
  (
    PersonNum INT
   ,WeekOf DATETIME
   ,ActivityType1 INT
   ,ActivityType2 INT
   ,Hours INT
  )
  
  INSERT INTO T VALUES(1,'11/2/2014',5,0,40)
  INSERT INTO T VALUES(1,'11/2/2014',1,0,5)
  INSERT INTO T VALUES(1,'11/2/2014',1,0,8)
  INSERT INTO T VALUES(1,'11/2/2014',2,1,6)
  INSERT INTO T VALUES(1,'11/2/2014',2,2,2)
  INSERT INTO T VALUES(1,'11/2/2014',2,3,9)
  INSERT INTO T VALUES(1,'11/2/2014',2,4,7)
  
  INSERT INTO T VALUES(1,'11/9/2014',5,0,40)
  INSERT INTO T VALUES(1,'11/9/2014',1,0,2)
  INSERT INTO T VALUES(1,'11/9/2014',1,0,6)
  INSERT INTO T VALUES(1,'11/9/2014',2,1,7)
  INSERT INTO T VALUES(1,'11/9/2014',2,2,2)
  INSERT INTO T VALUES(1,'11/9/2014',2,3,3)
  INSERT INTO T VALUES(1,'11/9/2014',2,4,5)
  </code></pre>
  
  <p>Method 1</p>
  
  <pre><code>SELECT 
    PersonNum
  , WeekOf
  , SUM(CASE WHEN ActivityType1 = 5 THEN Hours ELSE 0 END) AS Beginning
  , SUM(CASE WHEN ActivityType1 = 1 THEN Hours ELSE 0 END) AS Plus
  , SUM(CASE WHEN ActivityType1 = 2 AND ActivityType2 &lt;&gt; 3 THEN Hours ELSE 0 END) AS Minus
  , SUM(CASE WHEN ActivityType1 = 2 AND ActivityType2 = 3 THEN Hours ELSE 0 END) AS MinusSpecial
  FROM T
  GROUP BY 
    PersonNum
  , WeekOf
  </code></pre>
  
  <p>Method 2</p>
  
  <pre><code>SELECT
    PersonNum
  , WeekOf
  , [Beginning]
  , [Plus]
  , [Minus]
  , [Minus Special]
  FROM
  (
    SELECT
      PersonNum
    , WeekOf
    , 'Beginning' AS ColumnType
    , Hours
    FROM T
    WHERE
      ActivityType1 = 5 
    UNION
    SELECT
      PersonNum
    , WeekOf
    , 'Plus' AS ColumnType
    , Hours
    FROM T
    WHERE
      ActivityType1 = 1 
    UNION
    SELECT
      PersonNum
    , WeekOf
    , 'Minus' AS ColumnType
    , Hours
    FROM T
    WHERE
        ActivityType1 = 2 
    AND ActivityType2 &lt;&gt; 3
     UNION
    SELECT
      PersonNum
    , WeekOf
    , 'Minus Special' AS ColumnType
    , Hours
    FROM T
    WHERE
        ActivityType1 = 2 
    AND ActivityType2 = 3
      ) Data  
  PIVOT 
  (SUM(Hours)
  FOR ColumnType IN ([Beginning]                
                    ,[Plus]
                    ,[Minus]
                    ,[Minus Special])
  ) pvt 
  </code></pre>