如何创建保留不同记录的动态 PIVOT 查询？

保留不同记录的动态 PIVOT 查询

问题：

PIVOT 查询经常使用 MAX () 聚合来汇总数据，这可能会导致不同值的丢失。例如，在具有相同名称的多个活动记录的表中，PIVOT 可能会忽略 MAX() 值较低的记录。

目标：

创建动态 PIVOT保留不同记录及其相应百分比的查询

解决方案：

1. 引入 ROW_NUMBER() 分区： 在执行之前添加按名称列分区的 ROW_NUMBER()枢。这会将唯一的行号分配给不同的名称行，同时保留百分比排序。
2. 对分区数据进行透视： 使用 ROW_NUMBER() 作为 PIVOT 查询中的附加列。输出将匹配不同的名称行及其各自的活动和百分比。
3. 重新分组以进行动态聚合：数据透视后，按 Id、Code 和 ROW_NUMBER() 对数据进行分组。此步骤组合每个不同名称行的百分比值。

;with cte as
(
    select *, ROW_NUMBER() over (partition by name order by percentage desc) ROWNUM
    from A
),
cte2 as
(
    SELECT Id,Code,ROWNUM,James,James_,Sam,Sam_,Lisa,Lisa_
    FROM cte
    PIVOT(MAX(activity)
          FOR name IN (James,Sam,Lisa)) AS PVTTable PIVOT
          (
          MAX(percentage)
          FOR name1 IN (James_,Sam_,Lisa_)) AS PVTTable1
)
select Id, Code, MAX(James) James, MAX(James_) James_, MAX(Sam) Sam, MAX(Sam_) Sam_, MAX(Lisa) Lisa, MAX(Lisa_) Lisa_
from cte2
group by Id, Code, ROWNUM

动态查询生成：

对于动态查询生成，请替换逗号分隔的列表名称 (@name_concat, @name1_concat) 和 SELECT MAX() 聚合 (@select_aggs)，其变量可以是在运行时填充。

示例结果：

查询返回所需的输出，保留不同的名称行及其百分比：

Id  Code        James       James_  Sam         Sam_    Lisa    Lisa_
1   Prashant    Running     43.43   Cooking 1   73      Walking 90.34
1   Prashant    Stealing    0.00    Cooking     3.43    NULL    NULL
1   Prashant    Lacking     0.00    NULL        NULL    NULL    NULL

以上是如何创建保留不同记录的动态 PIVOT 查询？的详细内容。更多信息请关注PHP中文网其他相关文章！