將多筆資料合併為一筆顯示 - FOR XML PATH | SQL

By Casey | | 1 minutes
Sql

前情提要

本篇使用 AdventureWorks2016 資料庫，將每一個產品的利用 FOR XML PATH 將評論人顯示多筆資料。

步驟

  1. 先找出要使用合併的資料 (通常在 detail page)，先鎖定 ProductID = 937，較容易寫出完整 query
1
2
3
4

SELECT ','  + [Production].[ProductReview].ReviewerName 
FROM [Production].[ProductReview]
WHERE ProductID = 937
FOR XML PATH('')

可以看到評論 ProductId = 937 的人有 David 和 Jill

Imgur

  1. 在每一個產品中，顯示各個產品的評論的人並使用 FOR XML PATH 顯示
  • 將 WHERE ProductID = 937 拿掉
  • 加入 [Production].[ProductReview].ProductID = [Product].ProductID 和 FROM [Production].[Product]
1
2
3
4
5
6
7
8

	SELECT ProductID, 
	(
		SELECT ',' + [Production].[ProductReview].ReviewerName 
		FROM [Production].[ProductReview]
		WHERE [Production].[ProductReview].ProductID = [Product].ProductID
		FOR XML PATH('')
	) AS Reviewer
	FROM [Production].[Product]

Imgur

因為這一個例子的評論人並不多，所以先拿掉沒有評論的產品

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

SELECT *
FROM (
	SELECT ProductID, 
	(
		SELECT ',' + [Production].[ProductReview].ReviewerName 
		FROM [Production].[ProductReview]
		WHERE [Production].[ProductReview].ProductID = [Product].ProductID
		FOR XML PATH('')
	) AS Reviewer
	FROM [Production].[Product]
)A
WHERE Reviewer IS NOT NULL

這樣就很清楚的看到哪一個產品有被誰寫評價

Imgur

  1. 使用 STUFF()，拿掉最左邊的逗號
  • STUFF(原字串, 起始位置, 移除長度, 替換字串)
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

SELECT *
FROM (
	SELECT ProductID, 
	STUFF((
		SELECT ',' + [Production].[ProductReview].ReviewerName 
		FROM [Production].[ProductReview]
		WHERE [Production].[ProductReview].ProductID = [Product].ProductID
		FOR XML PATH('')
	), 1, 1, '') AS Reviewer
	FROM [Production].[Product]
)A
WHERE Reviewer IS NOT NULL

Imgur

參考資料

[SQL]將多筆資料合併為一筆顯示(FOR XML PATH)

[食譜好菜] SQL Server 使用「FOR XML」語法做欄位合併

comments powered by Disqus