时间戳

首页 > 解决方案 > 在 SQL Server 中，是否可以在不聚合的情况下对行进行透视？

tsql - 在 SQL Server 中，是否可以在不聚合的情况下对行进行透视？

问题描述

这是一个示例：假设每个命令的参数数量有限，我想将参数及其值表示为 named_indexed 列。

--drop table TCommand
--drop table TParam

Create Table TCommand (
CommandID INT,
CommandName NCHAR(20),
Description NVARCHAR(100)
);

Create Table TParam (
CommandID INT,
ParamName NCHAR(20),
ParamValue NCHAR(50)
);

insert into TCommand Values(1, 'C1', 'Desc for command C1')
insert into TCommand Values(2, 'C2', 'Desc for command C2')
insert into TCommand Values(3, 'C3', 'Desc for command C3')

insert into TParam Values (1, 'Pa', 'xa1')
insert into TParam Values (1, 'Pb', 'yb1')
insert into TParam Values (1, 'Pc', 'zc1')

insert into TParam Values (2, 'Px', 'xa2')
insert into TParam Values (2, 'Py', 'yb2')

insert into TParam Values (3, 'Pt', 'xa3')
insert into TParam Values (3, 'Pu', 'yb3')

select tc.*, tp.ParamName, tp.ParamValue 
from TCommand tc
join TParam tp on tp.CommandID=tc.CommandID
order by tc.CommandName, tp.ParamName

结果：

CommandID   CommandName Description          ParamName ParamValue
----------- ----------- -------------------- --------- ----------
1           C1          Desc for command C1  Pa        xa1       
1           C1          Desc for command C1  Pb        yb1       
1           C1          Desc for command C1  Pc        zc1       
2           C2          Desc for command C2  Px        xa2       
2           C2          Desc for command C2  Py        yb2       
3           C3          Desc for command C3  Pt        xa3       
3           C3          Desc for command C3  Pu        yb3

这是我想要获得的格式。

CommandID   CommandName Description          ParamName_1 ParamValue_1 ParamName_2 ParamValue_2 ParamName_3 ParamValue_3
----------- ----------- -------------------- ----------- ------------ ----------- ------------ ----------- ------------
1           C1          Desc for command C1  Pa          xa1          Pb          yb1          Pc          zc1   
2           C2          Desc for command C2  Px          xa2          Py          yb2          NULL        NULL      
3           C3          Desc for command C3  Pt          xa3          Pu          yb3          NULL        NULL

我应该写什么查询？由于缺少聚合功能（我认为我不需要），早期尝试使用 PIVOT 失败。

提前致谢。

标签： tsql

解决方案

您需要在此处创建两个数据透视查询：一个用于 ParamName，另一个用于 ParamValue。在表q1和q2派生表中，我只为每个数据透视表选择了所需的列（如果我只q在两个查询中使用，我会在相关列中获得带有 NULL 的额外行）。

为了能够连接两个查询，您需要一个表示参数位置的列（RowNum在下面的查询中命名）。

如果数据透视列的每个值都有一个值，则可以使用聚合函数，例如 MIN 或 MAX（忽略 NULL 值并保留单个输入值）。

因此，您可以使用以下查询：

;WITH q AS (
    SELECT tp.CommandID,
        ROW_NUMBER() OVER (PARTITION BY tp.CommandID ORDER BY tp.ParamName) AS RowNum,
        tp.ParamName, tp.ParamValue
    FROM dbo.TParam tp
)
SELECT tc.CommandID, tc.CommandName, tc.Description, 
    x1.ParamName_1, x2.ParamValue_2, x1.ParamName_2, x2.ParamValue_2, x1.ParamName_3, x2.ParamValue_3
FROM dbo.TCommand tc
LEFT JOIN (
    SELECT p.CommandID, p.[1] AS ParamName_1, p.[2] AS ParamName_2, p.[3] AS ParamName_3
    FROM (SELECT q.CommandID, q.RowNum, q.ParamName FROM q) q1
    PIVOT (MAX(ParamName) FOR RowNum IN ([1],[2],[3])) p
) x1 ON x1.CommandID = tc.CommandID
LEFT JOIN (
    SELECT p.CommandID, p.[1] AS ParamValue_1, p.[2] AS ParamValue_2, p.[3] AS ParamValue_3
    FROM (SELECT q.CommandID, q.RowNum, q.ParamValue FROM q) q2
    PIVOT (MAX(ParamValue) FOR RowNum IN ([1],[2],[3])) p
) x2 ON x2.CommandID = tc.CommandID

推荐阅读