SQL Server PIVOT 可能是什么？

Question

SELECT Name1, Name2, Value FROM mytable 这个语句返回的结果如下：

Name1 Name2 Value
A     P1     1
A     P2     1
A     P3     2
B     P1     3
B     P2     1
B     P4     1

要将其转换为以下格式：

       A     B
P1     1     4
P2     1     1
P3     2     null
P4     null  1

谢谢。

- user683302

---

你好 @user！你正在使用哪个数据库管理系统？ - Josh Darnell

A和B是固定的常量列表吗？ - Neil

A和B不是固定的，顶部选择可能会返回更多不仅仅是A和B，有时它将是A，B，C，... X例如。 - user683302

@jadarnel27 不是你的错，直到Tony编辑它并且问题没有标记为[tag:sql-server-2005]，而且仅仅因为OP恰好使用了关键词PIVOT并不意味着他们知道它只在2005+版本中可用。很多人在使用SQL Server 2000时会问“如何旋转这个？” :-) - Aaron Bertrand

@user683302 我认为这是应该包含在问题中的重要信息 - 不清楚可能存在除 A 和 B 之外的其他值。另外，当存在两行如下的情况时会发生什么：A，P1，1 和 A，P1，4？详细说明表格上的唯一约束条件将有助于其他人确定必要的聚合方式。 - Aaron Bertrand

Answer 1

由于您正在使用 SQL Server 2005，这里是代码：

DECLARE @cols VARCHAR(1000)
DECLARE @sqlquery VARCHAR(2000)

SELECT  @cols = STUFF(( SELECT distinct  ',' + QuoteName([Name1])
                        FROM myTable FOR XML PATH('') ), 1, 1, '') 


SET @sqlquery = 'SELECT * FROM
      (SELECT Name2, Name1, Value
       FROM myTable ) base
       PIVOT (Sum(Value) FOR [Name1]
       IN (' + @cols + ')) AS finalpivot'

EXECUTE ( @sqlquery )

无论你有多少不同的状态，这都能起作用。它使用PIVOT动态组装查询。在SQL Server中，唯一能够使用动态列进行PIVOT的方法是通过动态地组装查询语句来实现。

其他例子：

• 在T-SQL中旋转数据
• 如何通过与SQL Server中的单个表连接来构建汇总？
• https://stackoverflow.com/q/8248059/570191

- Adriano Carneiro

---

@Martin 它可以防止注入攻击！ - Adriano Carneiro

这类似于一个帖子 - http://stackoverflow.com/questions/6849678/how-do-i-build-a-summary-by-joining-to-a-single-table-with-sql-server/6849795#6849795，实际上就是我提到的注入攻击 :)。 - JonH

Answer 2

我在工作中没有运行 SQL Server，所以这可能不是完全正确的语法，但一种方法是交叉表。

SELECT name2
     , SUM(CASE WHEN name1 = 'A' THEN value END) AS A
     , SUM(CASE WHEN name1 = 'B' THEN value END) AS B
FROM table
GROUP BY name2

对于可变数量的列，您可以使用动态SQL：

DECLARE @sql varchar(max)
SELECT @sql = COALESCE(@sql+',','') + 'SUM(CASE WHEN nane1 = '''+name1+''' THEN value END) AS ['+name1']' FROM table

SET @sql = 'SELECT name2, '+@sql+' FROM table GROUP BY name2'

EXEC(@sql)

- Aaron Silverman

---

当你的代码正在编译时，有很多更有建设性的事情可以做 ;) 我也是在工作时发表了那条评论，哈哈。 - Josh Darnell

Answer 3

您可以使用PIVOT子句。您的查询可能如下所示：

PIVOT

WITH Source as (
    SELECT Name1, Name2, [Value]
    FROM mytable 
)

SELECT Name2, CASE WHEN A IS NOT NULL THEN A ELSE 'your string' END As A
, CASE WHEN B IS NOT NULL THEN B ELSE 'your string' END As B
FROM (
        SELECT Name2, Name1, [Value]
        FROM Source
) s
PIVOT 
(
    MAX([Value]) FOR Name1 IN (A, B) -- any other Name1 would go here
) p

使用您提供的样本数据，我的结果如下：

P1  1           3
P2  1           1
P3  2           your string
P4  your string 1

编辑：

由于您有未知数量的列，您需要考虑使用动态SQL，并且这里有几个关于PIVOT的SO答案。

SQL Server 2005对未知列进行PIVOT

透视表和连接列的SQL问题

- Taryn

---

问题在于楼主无法将输出限制为A、B。正如他在评论中所述，可能还有C、...、X等其他输出。 - Aaron Bertrand

我进行了一些修改，并通过使用此示例和https://dev59.com/dnVC5IYBdhLWcg3w21Mq成功使其工作。最后一个问题是，如何用另一个字符串替换NULL值。我尝试了CASE语句，但它只替换具有值的“Value”字段，而不是NULL值。 - user683302

我使用CASE更新了上面的PIVOT查询，并将NULL值更改为字符串。 - Taryn

在这种情况下，我更喜欢使用COALESCE而不是CASE，否则很好。 - Andriy M

@Andriy 我也在很多情况下使用 COALESCE。我以前在这里使用它时受到了批评，所以这次我使用了 CASE。 - Taryn