SQL Server で複数行の列をグループ化してCSV出力。ついでにJSON。

SQLで複数行の列をグループ化してカンマ区切りで出力してみます。FOR XML PATH で(”) を指定すると囲み要素は生成されずに半角スペース(‘ ‘)で区切られますので、あとは半角スペースをカンマに置換するだけです。

以下のようなデータがあって

20140424-01

 

ItemIdでグループ化して、Colorをカンマ区切りにする場合は、こんな感じです。

SELECT 
	ItemId,
	REPLACE((SELECT Color AS [data()] 
	FROM	ItemDetails
	WHERE D.ItemId = ItemId
	FOR XML PATH ('')), ' ', ',') AS Colors
FROM
	[ItemDetails] AS D
GROUP BY ItemId

 

Colorに半角スペース(‘ ‘)が入ってる場合、REPLACE使うなら以下ですかね。

SELECT 
	ItemId,
	REPLACE(REPLACE(REPLACE((SELECT Color AS [data()] 
	FROM	ItemDetails
	WHERE D.ItemId = ItemId
	FOR XML PATH), '</row><row>', '","'), '<row>', '"'), '</row>', '"') AS Colors
FROM
	[ItemDetails] AS D
GROUP BY ItemId

 

ついでに上記を[]で囲えば、JSONの出来上がり。

SELECT 
	ItemId,
	REPLACE(REPLACE(REPLACE((SELECT Color AS [data()] 
	FROM	ItemDetails
	WHERE D.ItemId = ItemId
	FOR XML PATH), '</row><row>', '","'), '<row>', '["'), '</row>', '"]') AS Colors

FROM
	[ItemDetails] AS D
GROUP BY ItemId

 

上の3つのクエリを実行した結果です。

20140424-02

Comments are closed.

Post Navigation