Comment empiler des données avec SQL Server

Question

D'accord, j'ai des données avec un grand nombre de colonnes, disons 400.

UID, ID, ID, var1, var2, . . . var400

1, 23,  4651,  0,     0, . . .   1
2, 47,  8567,  1,     1, . . .   5

J'ai besoin qu'elles soient empilées de sorte qu'elles ressemblent à ceci :

UID, ID, ID,    Variable,  Valeur
1,   23, 4651    var1,       0
1,   23, 4651    var2,       0
. . .
1,   23, 4651    var400,     1
2,   47, 8567,   var1        1
2,   47, 8567,   var2        1
. . . 
2,   47, 8567,   var400      5

Il doit y avoir un moyen relativement facile d'effectuer cette transformation. Mais je ne peux pas penser à ce que c'est. Des idées ?

Answer 1

D'accord, cela fonctionnera pour SQL Server 2005+. Vous avez la version statique de UNPIVOT:

SELECT UID, ID1, ID2, Variable, Value
FROM (SELECT * FROM YourTable) T
UNPIVOT (Value FOR Variable IN (var1, var2, ....var400)) AS U;

Voici un sqlfiddle avec une démo de ceci.

Cela fonctionnera, mais vous devez écrire chacune de vos 400 colonnes. Si vous ne voulez pas le faire (mais je recommande que vous le fassiez), vous pouvez extraire d'abord les noms des colonnes et utiliser du SQL dynamique :

DECLARE @sql NVARCHAR(MAX), @cols NVARCHAR(MAX)

SELECT @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(COLUMN_NAME) 
                      FROM INFORMATION_SCHEMA.COLUMNS
                      WHERE TABLE_NAME = 'YourTable'
                      AND COLUMN_NAME LIKE 'Var%'
               FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

SET @sql = '
SELECT UID, ID1, ID2, Variable, Value
FROM (SELECT * FROM YourTable) T
UNPIVOT (Value FOR Variable IN ('+@cols+')) AS U;'

EXEC(@sql)

Et voici un sqlfiddle avec la version dynamique.

Answer 2

Si vous utilisez SQL Server 2008+, alors vous pouvez utiliser CROSS APPLY avec VALUES:

select t.uid,
  t.id1,
  t.id2,
  c.variable,
  c.value
from YourTable t
cross apply
(
  values 
    ('var1', t.var1),
    ('var2', t.var2),
    ('var3', t.var3)
) c (variable, value);

Voir SQL Fiddle avec Demo Merci @Lamak pour le fiddle