FOR XML PATH
SQL指令_將多筆資料合併為一筆複合字串顯示(FOR XML PATH)，再將一筆複合字串拆成多欄位
2018/12/28 16:28:24
1
29993
SQL指令_將多筆資料合併為一筆複合字串顯示(FOR XML PATH)，再將一筆複合字串拆成多欄位
| 簡介 |
介紹如何在資料庫中操作將多筆資料合併為一筆複合字串顯示，再將一筆複合字串拆成多欄位的需求。 |
| 作者 |
陳淑敏 |
1. 前言
當有需要將多筆資料轉合併成一筆複合字串，即可使用FOR XML PATH，
而將一筆複合字串拆成多欄位，
就是將'123-456'轉成'<n>123</n><n>456</n>'後再轉型成SQLXML型別，
即可用.value('(/n)[1]', 'varchar(10)')取出'123'。
此運算式適用於SQL Server 2008以上版本。
2. 開始前準備
實作此運算式是建立於以下版本的環境： SQL Server 2017
3. 預期呈現的結果
將AREA_NAME及 AREA_SUM此兩個欄位多筆資料，轉成一筆多個欄位的資料，
需注意：欄位需為已知固定數量，且需大於AREA_NAME數量。
4. 實作演練
先建立資料表[Area]，執行以下語法即可：
USE [Test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Area](
[REG_UNIT] [smallint] NULL,
[REG_UNIT_NAME] [varchar](20) NULL,
[AREA_CODE] [smallint] NULL,
[AREA_NAME] [varchar](20) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Area] ([REG_UNIT], [REG_UNIT_NAME], [AREA_CODE], [AREA_NAME]) VALUES (55, '昕力保險', 10, '南京處')
INSERT [dbo].[Area] ([REG_UNIT], [REG_UNIT_NAME], [AREA_CODE], [AREA_NAME]) VALUES (55, '昕力保險', 10, '南京處')
INSERT [dbo].[Area] ([REG_UNIT], [REG_UNIT_NAME], [AREA_CODE], [AREA_NAME]) VALUES (55, '昕力保險', 10, '南京處')
INSERT [dbo].[Area] ([REG_UNIT], [REG_UNIT_NAME], [AREA_CODE], [AREA_NAME]) VALUES (55, '昕力保險', 10, '南京處')
INSERT [dbo].[Area] ([REG_UNIT], [REG_UNIT_NAME], [AREA_CODE], [AREA_NAME]) VALUES (55, '昕力保險', 10, '南京處')
INSERT [dbo].[Area] ([REG_UNIT], [REG_UNIT_NAME], [AREA_CODE], [AREA_NAME]) VALUES (55, '昕力保險', 10, '南京處')
INSERT [dbo].[Area] ([REG_UNIT], [REG_UNIT_NAME], [AREA_CODE], [AREA_NAME]) VALUES (55, '昕力保險', 10, '南京處')
INSERT [dbo].[Area] ([REG_UNIT], [REG_UNIT_NAME], [AREA_CODE], [AREA_NAME]) VALUES (55, '昕力保險', 10, '南京處')
INSERT [dbo].[Area] ([REG_UNIT], [REG_UNIT_NAME], [AREA_CODE], [AREA_NAME]) VALUES (55, '昕力保險', 10, '南京處')
INSERT [dbo].[Area] ([REG_UNIT], [REG_UNIT_NAME], [AREA_CODE], [AREA_NAME]) VALUES (55, '昕力保險', 10, '南京處')
INSERT [dbo].[Area] ([REG_UNIT], [REG_UNIT_NAME], [AREA_CODE], [AREA_NAME]) VALUES (55, '昕力保險', 20, '新竹處')
INSERT [dbo].[Area] ([REG_UNIT], [REG_UNIT_NAME], [AREA_CODE], [AREA_NAME]) VALUES (55, '昕力保險', 20, '新竹處')
INSERT [dbo].[Area] ([REG_UNIT], [REG_UNIT_NAME], [AREA_CODE], [AREA_NAME]) VALUES (55, '昕力保險', 20, '新竹處')
INSERT [dbo].[Area] ([REG_UNIT], [REG_UNIT_NAME], [AREA_CODE], [AREA_NAME]) VALUES (55, '昕力保險', 20, '新竹處')
INSERT [dbo].[Area] ([REG_UNIT], [REG_UNIT_NAME], [AREA_CODE], [AREA_NAME]) VALUES (55, '昕力保險', 20, '新竹處')
INSERT [dbo].[Area] ([REG_UNIT], [REG_UNIT_NAME], [AREA_CODE], [AREA_NAME]) VALUES (55, '昕力保險', 30, '一心處')
INSERT [dbo].[Area] ([REG_UNIT], [REG_UNIT_NAME], [AREA_CODE], [AREA_NAME]) VALUES (55, '昕力保險', 30, '一心處')
INSERT [dbo].[Area] ([REG_UNIT], [REG_UNIT_NAME], [AREA_CODE], [AREA_NAME]) VALUES (55, '昕力保險', 30, '一心處')
INSERT [dbo].[Area] ([REG_UNIT], [REG_UNIT_NAME], [AREA_CODE], [AREA_NAME]) VALUES (55, '昕力保險', 30, '一心處')
INSERT [dbo].[Area] ([REG_UNIT], [REG_UNIT_NAME], [AREA_CODE], [AREA_NAME]) VALUES (55, '昕力保險', 30, '一心處')
INSERT [dbo].[Area] ([REG_UNIT], [REG_UNIT_NAME], [AREA_CODE], [AREA_NAME]) VALUES (55, '昕力保險', 30, '一心處')撈取已建立完成的資料表[Area]。
SELECT * FROM [dbo].[Area] 
撰寫FOR XML PATH運算子，撈取資料表[Area]的AREA_NAME欄位及AREA_NAME加總數量由多筆資料合併為一筆複合字串，請見以下語法：
select distinct REG_UNIT_NAME,
(
select Convert(varchar,AREA_NAME) + ',' + Convert(varchar,count(AREA_NAME))
from [dbo].[Area]
group by REG_UNIT_NAME, AREA_NAME
FOR XML PATH('n')
) as result
into #temp
from [dbo].[Area]
select * from #temp 
再將一筆複合字串拆成多欄位，此' <n>一心處,6</n>'資料中因地區與數量用逗號區隔，
但也需將數量以一欄位呈現，故加上replace(result, ',','</n><n>')語法即可，
之後再轉型成SQLXML型別，即可用.value('(/n)[1]', 'varchar(20)')取出'一心處'，請見以下語法：
select REG_UNIT_NAME,
(convert(xml, replace(result, ',','</n><n>'))).value('(/n)[1]', 'varchar(20)') as 'AREA_CODE1',
(convert(xml, replace(result, ',','</n><n>'))).value('(/n)[2]', 'varchar(20)') as 'AREA_SUM1',
(convert(xml, replace(result, ',','</n><n>'))).value('(/n)[3]', 'varchar(20)') as 'AREA_CODE2',
(convert(xml, replace(result, ',','</n><n>'))).value('(/n)[4]', 'varchar(20)') as 'AREA_SUM2',
(convert(xml, replace(result, ',','</n><n>'))).value('(/n)[5]', 'varchar(20)') as 'AREA_CODE3',
(convert(xml, replace(result, ',','</n><n>'))).value('(/n)[6]', 'varchar(20)') as 'AREA_SUM3',
(convert(xml, replace(result, ',','</n><n>'))).value('(/n)[7]', 'varchar(20)') as 'AREA_CODE4',
(convert(xml, replace(result, ',','</n><n>'))).value('(/n)[8]', 'varchar(20)') as 'AREA_SUM4',
(convert(xml, replace(result, ',','</n><n>'))).value('(/n)[9]', 'varchar(20)') as 'AREA_CODE5',
(convert(xml, replace(result, ',','</n><n>'))).value('(/n)[10]', 'varchar(20)') as 'AREA_SUM5'
from #temp 
4. 參考來源
l https://dotblogs.com.tw/kevinya/2012/06/01/72553 [SQL]將多筆資料合併為一筆顯示(FOR XML PATH)
l https://blog.darkthread.net/blog/string-split-on-oracle-and-sql 將複合字串拆成多欄位－以ORACLE及SQL為例
相關文章
