NULL または空の文字列を処理するデータ変換に動的 SQL PIVOT を使用する

この記事では、テーブル内のデータをカテゴリピボット形式に変換する方法について説明します。この形式では、NULL 値は NULL または空の文字列として表されます。これは、動的 SQL PIVOT クエリを通じて実現できます。

動的変換に PIVOT を使用する

次の手順は、動的 PIVOT クエリを作成するプロセスの概要を示しています。

一時テーブルの作成: 元のデータを保存し、必要な列を定義するための一時テーブルを作成します。
動的列リストを生成します: サブクエリを使用してさまざまなカテゴリを抽出し、それらをカンマ区切りの文字列に連結します。
PIVOT クエリを構築します: 生成された列のリストを PIVOT パラメーターとして使用して、PIVOT クエリを構築します。 MAX()集計関数は、各カテゴリの最大値を取得します。
クエリの実行: 動的 PIVOT クエリを実行して、変換された結果を生成します。

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

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.category) 
            FROM temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT date, ' + @cols + ' from 
            (
                select date
                    , amount
                    , category
                from temp
           ) x
            pivot 
            (
                 max(amount)
                for category in (' + @cols + ')
            ) p '


execute(@query)</code>

ログイン後にコピー

結果

結果のテーブルには、日付が行として、一意のカテゴリを表す列が含まれます。 Null 値は、目的の出力に応じて、NULL または空の文字列を介して処理できます。

結果の例:

<code>日期                        ABC         DEF    GHI
2012-01-01 00:00:00.000     1000.00     NULL    NULL
2012-02-01 00:00:00.000     NULL        500.00  800.00
2012-02-10 00:00:00.000     NULL        700.00  NULL
2012-03-01 00:00:00.000     1100.00     NULL    NULL</code>

ログイン後にコピー

How to Use Dynamic SQL PIVOT for Data Transformation with NULL or Empty String Handling?