FOR XML PATH

SQL指令_將多筆資料合併為一筆複合字串顯示(FOR XML PATH)，再將一筆複合字串拆成多欄位

陳淑敏 2018/12/28 16:28:24

1 28838

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