title: sql server pivot : group by with dynamic columns
body: <p>I have done a pivot on a table, generating dynamic columns :</p>
  
  <pre><code>DECLARE @cols AS NVARCHAR(MAX);
  DECLARE @query AS NVARCHAR(MAX);
  
  select @cols = STUFF((SELECT distinct ',' +
                          QUOTENAME(Replace(variable,char(CAST(0x0016 as int)),'')) val
                        FROM TABLEDATA
                        ORDER BY val asc
                        FOR XML PATH(''), TYPE
                       ).value('.', 'NVARCHAR(MAX)') 
                          , 1, 1, '');
  
  SELECT @query =
  
  'SELECT
    time,
    country,
    disease,
    ' + @cols + '
  FROM
    TABLEDATA
  PIVOT 
  (
    MAX(value) 
    FOR variable IN( ' + @cols + ' )' +
  ' ) AS p; ';
  
   execute(@query);
  </code></pre>
  
  <p>It returns the following result :</p>
  
  <pre><code>time | country | disease | indicateur1 | indicateur2 | ... | indicateur14 | ... | indicateurn
  -----------------------------------------------------------------------------------------
  2001 | FRANCE  | MAL     | 0.003       |             |     |              |     |
  2002 | FRANCE  | AID     |             | 0.45        |     |              |     |
  1997 | ITALY   | HIV     | 0.307       |             |     |              |     |
  1999 | ROMANIA | MAL     | 1.044       |             |     |              |     |
  2003 | ROMANIA | AID     |             |             |     | 0.77         |     |
  2003 | ROMANIA | AID     | 0.872       |             |     |              |     |
  2004 | ROMANIA | HIV     |             | 0.921       |     |              |     |
  2002 | IRELAND | MAL     |             | 0.008       |     |              |     | 
  </code></pre>
  
  <p>But I need to group by the first 3 columns to get the following :</p>
  
  <pre><code>time | country | disease | indicateur1 | indicateur2 | ... | indicateur14 | ... | indicateurn
  -----------------------------------------------------------------------------------------
  2001 | FRANCE  | MAL     | 0.003       |             |     |              |     |
  2002 | FRANCE  | AID     |             | 0.45        |     |              |     |
  1997 | ITALY   | HIV     | 0.307       |             |     |              |     |
  1999 | ROMANIA | MAL     | 1.044       |             |     |              |     |
  2003 | ROMANIA | AID     | 0.872       |             |     | 0.77         |     |
  2004 | ROMANIA | HIV     |             | 0.921       |     |              |     |
  2002 | IRELAND | MAL     |             | 0.008       |     |              |     |
  </code></pre>
  
  <p>So I need to <code>group by time, country, disease</code>, but as my columns from the pivot are dynamics, I can't manage to find a solution...</p>
  
  <p>Here is an <a href="http://www.sqlfiddle.com/#!3/81618f/1/0" rel="nofollow">SQL fiddle</a></p>