How to stack data with SQL Server

Question

Okay, so I have data with some large number of columns, say 400.

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

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

I need it stacked so that it looks like this:

UID, ID, ID,    Variable,  Value
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

There must be some relatively easy way to perform this transformation. But I can't think of what it is. Anyone have any ideas?

Answer 1

Ok, this will work for SQL Server 2005+. You have the static version of UNPIVOT:

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

Here is an sqlfiddle with a demo of this.

This will work, but you need to write every one of your 400 columns. If you don't want to do that (but I recommend that you do), you could extract the column names first and use dynamic SQL:

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)

And here is an sqlfiddle with the dynamic version.