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>

には合計列が含まれます:

合計列を追加するには、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>

以上がPIVOT を使用して SQL でカウント値を動的に生成して列を設定する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。