Servidor SQL pivot: agrupar por con columnas dinámicas
He hecho un pivote en una tabla, generando columnas dinámicas:
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);
Devuelve el siguiente resultado:
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 | | | |
Pero necesito agrupar por las primeras 3 columnas para obtener lo siguiente:
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 | | | |
Así que necesitogroup by time, country, disease
, pero como mis columnas desde el pivote son dinámicas, no puedo encontrar una solución ...
Aquí hay unaViolín de SQL