PIVOT 関数を使用して SQL で列を動的に生成する方法

PIVOT 関数を使用した SQL での列の動的生成

この記事では、顧客特典データベースでさまざまな特典タイプを表す列を SQL で動的に作成するという課題について説明します。 報酬の種類は固定されておらず、時間の経過とともに変更される可能性があります。 SQL PIVOT 関数を使用したソリューションを検討し、静的アプローチと動的アプローチの両方を示し、包括的な結果を得るために合計列の追加を含めます。

問題:

顧客、その報酬、報酬の種類に関するデータベース テーブルがあります。 報酬の種類は動的です。追加または削除できます。 目標は、各特典タイプの列を含む結果セットを生成し、各顧客のその特典タイプの数を示すことです。

静的ソリューション (報酬タイプの固定数):

報酬のタイプが事前にわかっている場合は、単純な PIVOT クエリで十分です。

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;

このクエリはうまく機能しますが、報酬タイプ (「ブロンズ」、「シルバー」など) が事前定義されている場合に限られます。

動的ソリューション (報酬タイプの数が不明):

報酬の種類を動的に増やすには、動的 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);

このコードは SQL クエリを動的に構築し、customerrewards テーブルから列リストを構築します。 これにより、任意の数の報酬タイプに適応できます。

合計列の追加:

顧客ごとの報酬の合計数を含めるには、ROLLUP 関数を使用できます (静的バージョンと動的バージョンの両方を以下に示します)。

合計を含む静的バージョン:

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;

合計を含む動的バージョン:

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);

これらの動的 SQL の例は、有用な合計列を含む可変数の報酬タイプ列を含むレポートを生成するための堅牢かつ柔軟なソリューションを提供します。 特定のデータベース スキーマに一致するようにテーブル名と列名を変更することを忘れないでください。

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