SpaceghostAli SpaceghostAli - 4 months ago 10
SQL Question

How can I create a table using distinct values from a column as the column names for the new table?

I have a table with a Tag column with about 1000 distinct tags. I'd like to create a new table with a column for each distinct tag, the column name would be the tag value. Is there a nice and easy way to do this with T-SQL? Anything in SQL Server 2005 and up would be fine.

Here's an example of what I mean:

Table:
Tag Value Timestamp
tag1 123 ---
tag1 456 ---
tag2 854 ---
tag3 923 ---
.
.
.
tag90 324 ---

New Table:
tag1 tag2 tag3 ... tag90
123 854 923 324
456 ...


Update:

I'm aware of the limit (on the number of columns in a table), however the columns won't be sparse, and there will be no null or 0 values in any of the columns.

The reason I want this table is I'd like to run the SSAS clustering algorithm against the columns of this new table.

Answer

Use the PIVOT table operator, then use the INTO clause to create a new table from the pivoted columns. Something like this:

SELECT *
INTO newtablename
FROM ( SELECT tag, value FROM tags) AS t
PIVOT 
(
  MIN(value)
  FOR tag in ([tag1], [tag2], [tag3], [tag4])
) AS p;

See it in action here:


However, in your case, you have to use the dynamic sql to do this dynamically for any number of tags in your table, and not to list them manually. Like this:

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);


select @cols = STUFF((SELECT distinct ',' +
                        QUOTENAME(tag)
                      FROM tags
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '');

SELECT @query = 'SELECT *
                 INTO newtablename
                 FROM ( SELECT tag, value FROM tags) AS t
                 PIVOT 
                 (
                   MIN(value)
                   FOR tag in ( ' + @cols + ' )
                 ) AS p';

execute(@query);

See it in action here:

This will create a brand new table with the name newtablename that looks like:

| TAG1 | TAG2 | TAG3 | TAG4 |
-----------------------------
|  123 |  854 |  923 |   44 |
Comments