title: SQL Server Management Studio 2012 Pivot/Cross Tab Query
body: <p>I'm using SQL Server Management Studio 2012 and would like to create a pivot/cross tab query for a table with over 2300 rows.</p>
  
  <p>The table has 5 columns:</p>
  
  <pre><code> - name
   - group
   - status
   - date
   - count
  </code></pre>
  
  <p>There are about 580 distinct names.</p>
  
  <p>Each name is associated to 4 different groups (A, B, C, and D). </p>
  
  <p>Each group has a complete status of yes or no.</p>
  
  <p>A date is associated to each status when completed. Otherwise, status is NULL. </p>
  
  <p>The count column is only applicable to group B and D and is an integer value.</p>
  
  <p>SAMPLE A:</p>
  
  <pre><code>name    group  status   date    count
  A.A.1     A     yes     5/23    NULL
  A.A.1     B     yes     5/27    112
  A.A.1     C     yes     6/4     NULL
  A.A.1     D     yes     6/15    122
  A.B.2     A     yes     5/25    NULL
  A.B.2     B     yes     6/1     119
  A.B.2     C     no      NULL    NULL
  A.B.2     D     no      NULL    NULL
  </code></pre>
  
  <p>I am trying to display the status of each name as the field values across 11 columns :</p>
  
  <pre><code> - name
   - group A
   - group A date
   - group B
   - group B date
   - group B count
   - group C
   - group C date
   - group D
   - group D date
   - group D count
  </code></pre>
  
  <p>The 'name' column would have the 580 distinct names with their corresponding group data across A, B, C, and D.</p>
  
  <p>SAMPLE B:</p>
  
  <pre><code>nm      grp_A   A_day   grp_B   B_day   B_ct    grp_C   C_day   grp_D   D_day   D_ct
  A.A.1   yes     5/23    yes     5/27    112     yes     6/4     yes     6/15    122
  A.B.2   yes     5/25    yes     6/1     119     no      NULL    no      NULL    NULL
  </code></pre>
  
  <p>(<em>column names have been changed to fit into this question section's format</em>)</p>
  
  <p>Ultimately, the result should have all 580 distinct names in the first column and its corresponding status for each group, the date of completion (or NULL if it has not been completed yet), and the count for groups B and D.</p>
  
  <p>I've tried using a CASE statement, but it generates the names once for each group, resulting in the original table being spaced out across the 11 coulmns.</p>
  
  <p>SAMPLE C:</p>
  
  <pre><code>nm     grp_A    A_day   grp_B   B_day   B_ct    grp_C   C_day   grp_D   D_day   D_ct
  A.A.1   yes     5/23                                
  A.A.1                    yes    5/27    112         
  A.A.1                                             yes   6/4         
  A.A.1                                                            yes    6/15    122
  A.B.2   yes     5/25                                
  A.B.2                    yes    6/1     119         
  A.B.2                                             no    NULL            
  A.B.2                                                             no    NULL    NULL
  </code></pre>
  
  <p>What am I doing wrong? Please help!</p>
  
  <p>-- K-moj</p>