SQL Server で顧客ごとの購入商品をカンマ区切りでリストする方法（STRING_AGG 関数と FOR XML PATH 句を使用）
SQL Server で複数の行を 1 つのカンマ区切り値に変換する方法
方法 1: STRING_AGG 関数を使用する
STRING_AGG 関数は、SQL Server 2008 以降で使用できる集計関数です。この関数は、複数の値をカンマ区切りで連結した文字列を返します。
SELECT
CustomerID,
STRING_AGG(ProductName, ',') AS PurchasedProducts
FROM
SalesOrderHeaders
JOIN SalesOrderDetails
ON SalesOrderHeaders.SalesOrderID = SalesOrderDetails.SalesOrderID
GROUP BY
CustomerID;
このクエリは、SalesOrderHeaders テーブルと SalesOrderDetails テーブルを結合し、各顧客が購入した製品をカンマ区切りでリストした文字列を返します。
方法 2: FOR XML PATH 句を使用する
FOR XML PATH 句は、SQL Server 2005 以降で使用できる句です。この句は、XML 形式のデータセットを生成します。その後、この XML データセットをカンマ区切り文字列に変換することができます。
SELECT
CustomerID,
(
SELECT
'/ProductNames/ProductName'
FOR XML PATH(''), TYPE
)
AS PurchasedProducts
FROM
SalesOrderHeaders
JOIN SalesOrderDetails
ON SalesOrderHeaders.SalesOrderID = SalesOrderDetails.SalesOrderID
GROUP BY
CustomerID;
どちらの方法を選択するかは、ニーズによって異なります。 STRING_AGG 関数は、FOR XML PATH 句よりも簡潔で読みやすいコードです。ただし、FOR XML PATH 句は、より柔軟な形式設定オプションを提供します。
STRING_AGG 関数を使用する場合
SELECT
CustomerID,
STRING_AGG(ProductName, ',') AS PurchasedProducts
FROM
SalesOrderHeaders
JOIN SalesOrderDetails
ON SalesOrderHeaders.SalesOrderID = SalesOrderDetails.SalesOrderID
GROUP BY
CustomerID;
このクエリは、以下の結果を返します。
CustomerID PurchasedProducts
---------- ------------------
1 Product1, Product2, Product3
2 Product4, Product5
3 Product6
FOR XML PATH 句を使用する場合
SELECT
CustomerID,
(
SELECT
'/ProductNames/ProductName'
FOR XML PATH(''), TYPE
)
AS PurchasedProducts
FROM
SalesOrderHeaders
JOIN SalesOrderDetails
ON SalesOrderHeaders.SalesOrderID = SalesOrderDetails.SalesOrderID
GROUP BY
CustomerID;
CustomerID PurchasedProducts
---------- ------------------
1 <ProductNames><ProductName>Product1</ProductName><ProductName>Product2</ProductName><ProductName>Product3</ProductName></ProductNames>
2 <ProductNames><ProductName>Product4</ProductName><ProductName>Product5</ProductName></ProductNames>
3 <ProductNames><ProductName>Product6</ProductName></ProductNames>
説明
CustomerID列の値に基づいてデータをグループ化します。- STRING_AGG 関数または FOR XML PATH 句を使用して、各グループ内の
ProductName列の値をカンマ区切りで連結します。 - 結果として、各顧客 ID とその顧客が購入した製品のリストが表示されます。
このサンプルコードを参考に、お客様のニーズに合わせて調整してください。
SQL Server で複数の行を 1 つのカンマ区切り値に変換するその他の方法
STUFF 関数と SUBSTRING 関数を使用する
STUFF 関数と SUBSTRING 関数を使用して、文字列を操作してカンマ区切り値を作成することができます。この方法は、柔軟性がありますが、やや複雑になる可能性があります。
SELECT
CustomerID,
(
SELECT
SUBSTRING(
(
SELECT
STUFF(
(
SELECT
ProductName + ', '
FROM
SalesOrderDetails
WHERE
SalesOrderHeaders.SalesOrderID = SalesOrderDetails.SalesOrderID
ORDER BY
SalesOrderDetails.SalesOrderID
FOR XML PATH(''), TYPE
)
, 1, LEN(',')
) + ' '
FROM
SalesOrderHeaders
WHERE
CustomerID = @CustomerID
),
1,
LEN(
(
SELECT
ProductName + ', '
FROM
SalesOrderDetails
WHERE
SalesOrderHeaders.SalesOrderID = SalesOrderDetails.SalesOrderID
ORDER BY
SalesOrderDetails.SalesOrderID
FOR XML PATH(''), TYPE
)
) - 2
)
) AS PurchasedProducts
FROM
SalesOrderHeaders
WHERE
CustomerID = @CustomerID;
REPEAT 関数と REPLACE 関数を使用する
REPEAT 関数と REPLACE 関数を使用して、カンマを指定した回数繰り返す文字列を作成することができます。その後、この文字列を使用して、各行の値を連結することができます。この方法は、STUFF 関数と SUBSTRING 関数を使用する方法よりもシンプルですが、柔軟性が低くなります。
SELECT
CustomerID,
(
SELECT
REPLACE(
REPT(',', COUNT(*) - 1),
',',
(
SELECT
ProductName + ','
FROM
SalesOrderDetails
WHERE
SalesOrderHeaders.SalesOrderID = SalesOrderDetails.SalesOrderID
ORDER BY
SalesOrderDetails.SalesOrderID
FOR XML PATH(''), TYPE
)
)
AS PurchasedProducts
FROM
SalesOrderHeaders
WHERE
CustomerID = @CustomerID;
動的 SQL を使用して、複数の行を 1 つのカンマ区切り値に変換するクエリを生成することができます。この方法は、複雑なクエリを生成する必要がある場合に役立ちます。
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'
SELECT
CustomerID,
(
SELECT
ProductName + ','
FROM
SalesOrderDetails
WHERE
SalesOrderHeaders.SalesOrderID = SalesOrderDetails.SalesOrderID
ORDER BY
SalesOrderDetails.SalesOrderID
FOR XML PATH(''), TYPE
)
AS PurchasedProducts
FROM
SalesOrderHeaders
WHERE
CustomerID = @CustomerID
GROUP BY
CustomerID;
';
EXEC sp_executesql @sql, N'@CustomerID INT', @CustomerID = @CustomerID;
注意事項
上記の方法を使用する際には、以下の点に注意してください。
- パフォーマンス: 複数の行を 1 つのカンマ区切り値に変換する方法は、パフォーマンスに影響を与える可能性があります。大量のデータを処理する場合は、パフォーマンスを考慮する必要があります。
- 読みやすさ: 複雑な方法を使用すると、クエリが読みづらくなる可能性があります。可読性を高めるために、できるだけシンプルな方法を使用してください。
- 柔軟性: 必要に応じて、さまざまな方法を組み合わせて使用することができます。
sql-server sql-server-group-concat
