时间戳

首页 > 解决方案 > 没有聚合函数的动态 TSQL Pivot

sql - 没有聚合函数的动态 TSQL Pivot

问题描述

我有一张这样的表（'ExternalPersonRelationTable'）

个人身份 主题代码
4187 3
4187 278
4429 3
4429 4
4463 99
4464 174
4464 175

我想轮换数据，以便表中的每个人都获得一个列和每个主题代码的 TRUE/FALSE 值，即这样的表：

代码 4187 4429 4463 4464
3 真的 真的 错误的 错误的
4 错误的 真的 错误的 错误的
...
99 错误的 错误的 真的 错误的
...
174 错误的 错误的 错误的 真的
175 错误的 错误的 错误的 真的

我认为这是我应该使用 PIVOT 和动态 SQL 解决的问题，但恐怕我的经验仅限于使用 CTE 和简单的 JOIN，所以我很难 PIVOTing 数据，更不用说动态命名结果列。

SubjectCode 和 PersonId 最终将连接到其他表以获取它们的真实值，但对于示例，我认为它们是不必要的。

我该如何解决这个问题？

标签： sqlsql-servertsqlpivotdynamic-pivot

解决方案

样本数据

create table ExternalPersonRelationTable
(
  PersonId int,
  SubjectCode int
);

insert into ExternalPersonRelationTable (PersonId, SubjectCode) values
(4187, 3),
(4187, 278),
(4429, 3),
(4429, 4),
(4463, 99),
(4464, 174),
(4464, 175);

解决方案

从枢轴查询的（有限）静态版本作为参考开始。

select piv.SubjectCode as Code,
       isnull(convert(bit, piv.[4187]), 0) as [4187],
       isnull(convert(bit, piv.[4429]), 0) as [4429],
       isnull(convert(bit, piv.[4463]), 0) as [4463],
       isnull(convert(bit, piv.[4464]), 0) as [4464]
from ExternalPersonRelationTable epr
pivot (max(epr.PersonId) for epr.PersonId in ([4187],[4429],[4463],[4464])) piv;

识别动态部分并构建（并验证）它们。

-- constuct lists
declare @fieldList nvarchar(1000);
declare @pivotList nvarchar(1000);

with cte as
(
  select epr.PersonId
  from ExternalPersonRelationTable epr
  group by epr.PersonId
)
select @fieldList = string_agg('isnull(convert(bit, piv.['
                             + convert(nvarchar(10), cte.PersonId)
                             + ']), 0) as ['
                             + convert(nvarchar(10), cte.PersonId)
                             + ']', ', ') within group (order by cte.PersonId),
       @pivotList = string_agg('['
                             + convert(nvarchar(10), cte.PersonId)
                             + ']', ',') within group (order by cte.PersonId)
from cte;

-- validate lists
select @fieldList as FieldList;
select @pivotList as PivotList;

在最终查询中合并动态部分（并在开发阶段进行验证）。

-- construct query
declare @query nvarchar(3000) = 'select piv.SubjectCode as Code, '
                              + @fieldList
                              + 'from ExternalPersonRelationTable epr '
                              + 'pivot (max(epr.PersonId) for epr.PersonId in ('
                              + @pivotList
                              + ')) piv;';
      
-- validate query
select @query as Query;

运行动态查询。

-- run query
exec sp_executesql @query;

结果

Code  4187   4429   4463   4464
----  -----  -----  -----  -----
  3   True   True   False  False
  4   False  True   False  False
 99   False  False  True   False
174   False  False  False  True
175   False  False  False  True
278   True   False  False  False

小提琴以查看实际情况。

推荐阅读