如何动态透视表并保留零百分比的不同记录？

不同记录的动态 PIVOT 查询

问题：

考虑下表：

| Id | Code | percentage | name | name1 | activity |
|---|---|---|---|---|---|
| 1 | Prashant | 43.43 | James | James_ | Running |
| 1 | Prashant | 70.43 | Sam | Sam_ | Cooking |
| 1 | Prashant | 90.34 | Lisa | Lisa_ | Walking |
| 1 | Prashant | 0.00 | James | James_ | Stealing |
| 1 | Prashant | 0.00 | James | James_ | Lacking |
| 1 | Prashant | 73 | Sam | Sam_ | Cooking 1 |

使用在标准 PIVOT 查询中，保留具有 0.00 百分比的不同记录可能具有挑战性。 MAX 函数经常忽略这些行，导致结果不完整。

预期结果：

| 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查询中引入ROW_NUMBER()函数。此函数为每个名称组中的记录分配行号，确保保留百分比为 0.00 的记录。

;with cte as (
    select *, ROW_NUMBER() over (partition by name order by percentage desc) ROWNUM
    from table_name
),
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

说明：

• The第一个公共表表达式 (cte) 将 ROWNUM 列添加到表中，按百分比降序对每个名称组中的记录进行排序
• 然后第二个公共表表达式 (cte2) 使用 PIVOT 函数重构数据。
• 最后，我们按 Id、Code 和 ROWNUM 对结果进行分组，以合并透视结果并保留0.00百分比记录。

重要注意：

为了使查询动态化，我们可以将硬编码的 name 和 name1 值替换为可以在运行时填充的动态变量。这允许查询处理具有不同列数的表。

以上是如何动态透视表并保留零百分比的不同记录？的详细内容。更多信息请关注PHP中文网其他相关文章！