title: How to unpivot columns and GROUP BY based on values in SQL Server?
body: <p>I want to calculate distinct PID and VID counts per year.</p>
  
  <p>Condition:</p>
  
  <pre><code>1. Separate count for column A or B or C or D  having value 1  ( A_to_D) 
  2. Separate count for column E  having value 1  (E)
  3. Separate count for column F  having value 1   (F)
  4. Separate count for ALL A to F column are NULL  (ALL_NULL)
  </code></pre>
  
  <p>In the output I want a separate new column Alpha with the following values:</p>
  
  <pre><code>A_to_D, E, F and ALL_Null
  </code></pre>
  
  <p>Current output of table:</p>
  
  <pre><code>PID VID Flag    Date        A       B       C       D       E       F
  1   A1  0       10/17/2013  NULL    NULL    NULL    NULL    NULL    NULL
  2   A2  1       5/27/2014   1       NULL    NULL    1       NULL    NULL
  3   A3  NULL    2/23/2015   NULL    NULL    NULL    NULL    1       NULL
  4   A4  NULL    12/6/2013   NULL    0       NULL    NULL    NULL    NULL
  5   A5  NULL    7/14/2016   NULL    NULL    NULL    NULL    NULL    1
  6   A6  NULL    4/29/2015   NULL    1       1       NULL    NULL    NULL
  7   A7  1       9/30/2016   1       NULL    NULL    NULL    NULL    NULL
  8   A8  NULL    6/28/2016   NULL    NULL    NULL    NULL    NULL    NULL
  9   A9  1       11/20/2013  NULL    NULL    NULL    NULL    NULL    NULL
  10  A10 2       10/8/2015   NULL    1       NULL    NULL    NULL    NULL
  </code></pre>
  
  <p>Here:</p>
  
  <pre><code>select datepart(Year,date) ,Count(distinct PID) ,Count( distinct VID)
  from table
  where A is not null or B is Not NUll or C is not null 
     or D is not null or E is not Null or F is not null
  group by datepart(Year,date)
  </code></pre>
  
  <p>Expected output:</p>
  
  <pre><code>Year        Count_PID       Count_VID       Alpha    
  2013                                        A_to_D
  2013                                        E
  2013                                        F
  2013            2               2           ALL_NULL
  2014            1               1           A_to_D
  2014                                        E
  2014                                        F
  2014                                        ALL_NULL
  2015            2               2           A_to_D
  2015            1               1           E
  2015                                        F
  2015                                        ALL_NULL
  2016            2               2           A_to_D
  2016                                        E
  2016            1                           F   
  2016            1                           ALL_NULL
  </code></pre>