動的 SQL の例で PIVOT を使用する SQL Server
列の数が常に同じであるとは限らない、実際のタイプのシナリオで複雑な PIVOT 関数を使用する SQL Server。
前回の記事では、必要なすべての列がわかっている標準的なシナリオで PIVOT を使用する方法について書きました。実際のデータ セットに応じて列名を生成するために、同じデータを基に作成できるようになりました。
その記事の最初の部分はスキップしますが、ここで立ち上げて実行するための SQL を提供します。まず、データセットを作成しましょう。
CREATE TYPE c1bs_Piv AS TABLE(MyRow NVARCHAR(10),--Row IDMyCol NVARCHAR(10),--Value to become columnCalcCol INT--Value to be Summed)GODECLARE @Piv c1bs_PivINSERT INTO @PivSELECT '1','1',1 UNIONSELECT '1','s',1 UNIONSELECT '1','3',1 UNIONSELECT '2','2',6 UNIONSELECT '2','2',1 UNIONSELECT '2','3',1 UNIONSELECT '3','5',5 UNIONSELECT '3','2',1 UNIONSELECT '3','3',1 UNIONSELECT '3','3',3別の記事では、列名 (1、2、3) をハードコーディングしましたが、これが常に有効であるとは限りません。たとえば、データ セットを GUI に送信するときに、レコード セットを大きくする必要がある場合があります。それを一周して、段階的に見ていきましょう。
- 上記のように、データを表すテーブル タイプを作成します。
- テーブルを宣言し、そこにデータを挿入します (@Piv)
- 2 つの変数を宣言します。1 つは列名をピボットに保持するため、もう 1 つは SELECT リスト用です。この例では、ISNULL を使用して列名から NULL 値を取り除きます。また、COALESCE を使用して名前を文字列に連結しました。
- @Piv テーブルを使用して別の変数を宣言します。これらは後で渡されるパラメーターです。
- SQL を宣言します。ここで、列名と選択リストを単純な PIVOT の修正版に挿入します。
- 上記の SQL ステートメントを EXECUTE し、パラメーターと @PIV テーブルを渡します。
--Complex Pivot - unknown column names--Get Column Names for belowDECLARE @Cols NVARCHAR(MAX),@Sel NVARCHAR(MAX) SELECT @Cols=COALESCE(@Cols+',','')+'['+MyCol+']',--Concatenate the Columns @Sel =COALESCE(@Sel +',','')+'ISNULL(['+MyCol+'], 0) AS ['+MyCol+']' --Concatenate the Columns into a select listFROM @Piv GROUP BY MyCol
--Columns list displaySELECT @Cols ColumnList,@Sel SelectList
--Complex Pivot SQLDECLARE @Params NVARCHAR(MAX)='@Piv c1bs_Piv READONLY'DECLARE @SQL NVARCHAR(MAX)='SELECT [MyRow],'+@Sel+'FROM @PivPIVOT (SUM(CalcCol)FOR MyColIN ('+@Cols+')) AS MyTable'
--Execute SQLEXECUTE sp_executesql @SQL,--SQL String from above@Params,--Parameter list@Piv--Temp Table needs to be passed in, can only be read only?データセットは、データセットからの 2 つの追加の列 (5,s) を含むようになりました。
| MyRow | 1 | 2 | 3 | 5 | s |
| 1 | 1 | 0 | 1 | 0 | 1 |
| 2 | 0 | 7 | 1 | 0 | 0 |
| 3 | 0 | 1 | 4 | 5 | 0 |
