如何使用来自列的不同值作为新表的列名来创建表?

问题描述

我有一个带有Tag列的表格，其中包含大约1000个不同的标签.我想为每个不同的标签创建一个带有列的新表，该列名称将是标签值.使用T-SQL是否有一种简便的方法来做到这一点? SQL Server 2005及更高版本中的任何内容都可以.

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.

这是我的意思的示例:

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 ...

更新:

我知道限制(在表中的列数上)，但是列不会稀疏，并且任何列中都不会有null或0值.

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.

我想要此表的原因是我想针对此新表的列运行SSAS群集算法.

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

推荐答案

使用PIVOT表运算符，然后使用INTO子句从透视列创建新表.像这样:

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:

• SQL Fiddle演示

但是，对于您的情况，您必须使用动态sql对表中的任意数量的标签动态地执行此操作，而不是手动列出它们.像这样:

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:

• SQL小提琴演示

这将创建一个名称为newtablename的全新表，如下所示:

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

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

这篇关于如何使用来自列的不同值作为新表的列名来创建表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！