如何使用PIVOT函数在SQL中动态生成列？

使用 PIVOT 函数在 SQL 中动态生成列

本文解决了在 SQL 中动态创建列来表示客户奖励数据库中不同奖励类型的挑战。 奖励类型并不固定，可能会随着时间而变化。 我们将探索使用 SQL PIVOT 函数的解决方案，演示静态和动态方法，并包括添加总计列以获取综合结果。

问题：

我们有客户、他们的奖励以及奖励类型的数据库表。 奖励的类型是动态的；它们可以被添加或删除。 目标是生成一个结果集，其中包含每种奖励类型的一列，显示每个客户的该奖励类型的计数。

静态解决方案（奖励类型固定数量）：

如果事先知道奖励类型，一个简单的 PIVOT 查询就足够了：

<code class="language-sql">SELECT name, [Bronze], [Silver], [Gold], [Platinum], [AnotherOne]
FROM
(
  SELECT c.name, cr.description, r.typeid
  FROM customers c
  LEFT JOIN rewards r ON c.id = r.customerid
  LEFT JOIN customerrewards cr ON r.typeid = cr.typeid
) x
PIVOT
(
  COUNT(typeid)
  FOR description IN ([Bronze], [Silver], [Gold], [Platinum], [AnotherOne])
) p;</code>

此查询效果很好，但前提是奖励类型（“青铜”、“白银”等）已预先定义。

动态解决方案（奖励类型数量未知）：

对于动态数量的奖励类型，我们需要动态 SQL：

<code class="language-sql">DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(description)
                    FROM customerrewards
                    GROUP BY description, typeid
                    ORDER BY typeid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'');

SET @query = 'SELECT name,' + @cols + ' FROM 
             (
                SELECT c.name, cr.description, r.typeid
                FROM customers c
                LEFT JOIN rewards r ON c.id = r.customerid
                LEFT JOIN customerrewards cr ON r.typeid = cr.typeid
            ) x
            PIVOT 
            (
                COUNT(typeid)
                FOR description IN (' + @cols + ')
            ) p;';

EXECUTE(@query);</code>

此代码动态构建 SQL 查询，从 customerrewards 表构建列列表。 这允许适应任意数量的奖励类型。

添加总计列：

要包含每个客户的奖励总数，我们可以使用 ROLLUP 函数（静态和动态版本如下所示）：

静态版本总计：

<code class="language-sql">SELECT name, SUM([Bronze]) Bronze, SUM([Silver]) Silver, 
  SUM([Gold]) Gold, SUM([Platinum]) Platinum, SUM([AnotherOne]) AnotherOne
FROM 
(
  SELECT name, [Bronze], [Silver], [Gold], [Platinum], [AnotherOne]
  FROM
  (
    SELECT c.name, cr.description, r.typeid
    FROM customers c
    LEFT JOIN rewards r ON c.id = r.customerid
    LEFT JOIN customerrewards cr ON r.typeid = cr.typeid
  ) x
  PIVOT
  (
    COUNT(typeid)
    FOR description IN ([Bronze], [Silver], [Gold], [Platinum], [AnotherOne])
  ) p
) x
GROUP BY name WITH ROLLUP;</code>

动态版本总计：

<code class="language-sql">DECLARE @cols AS NVARCHAR(MAX),
    @colsRollup AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(description) 
                    FROM customerrewards
                    GROUP BY description, typeid
                    ORDER BY typeid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

SELECT @colsRollup 
      = STUFF((SELECT ', SUM(' + QUOTENAME(description) + ') AS ' + QUOTENAME(description)
                    FROM customerrewards
                    GROUP BY description, typeid
                    ORDER BY typeid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');


SET @query 
          = 'SELECT name, ' + @colsRollup + '
             FROM
             (
                SELECT name,' + @cols + ' FROM 
                 (
                    SELECT c.name, cr.description, r.typeid
                    FROM customers c
                    LEFT JOIN rewards r ON c.id = r.customerid
                    LEFT JOIN customerrewards cr ON r.typeid = cr.typeid
                ) x
                PIVOT 
                (
                    COUNT(typeid)
                    FOR description IN (' + @cols + ')
                ) p 
              ) x1
              GROUP BY name WITH ROLLUP;';

EXECUTE(@query);</code>

这些动态 SQL 示例提供了强大而灵活的解决方案，用于生成具有可变数量奖励类型列（包括有用的总计列）的报告。 请记住调整表和列名称以匹配您的特定数据库架构。

以上是如何使用PIVOT函数在SQL中动态生成列？的详细内容。更多信息请关注PHP中文网其他相关文章！