SQLSERVER2005將查詢結果轉換為字串[接上一篇文章]

--查詢分組號為6的子孫子節點 with RTD1 as(select id ,pid from UserGroup),RTD2 as(select * from RTD1 where id=6union allselect RTD1.* from RTD2 inner join RTD1 on RTD2.id=RTD1.PID)select * from RTD2 --現在想進一步將查詢結果轉換為逗號分隔的字

--查詢分組號為6的子孫子節點

with 
	RTD1 as(
	select id ,pid from UserGroup
	),
	RTD2 as(
	select * from RTD1 where id=6
	union all
	select RTD1.* from RTD2 inner join RTD1 
	on RTD2.id=RTD1.PID
	)
select * from RTD2

--現在想進一步將查詢結果轉換為逗號分隔的字串

--查詢分組號為6的子孫子節點

with 
	RTD1 as(
	select id ,pid from UserGroup
	),
	RTD2 as(
	select * from RTD1 where id=6
	union all
	select RTD1.* from RTD2 inner join RTD1 
	on RTD2.id=RTD1.PID
	)
--select * from RTD2
select ','+cast(id as nvarchar) from RTD2 for xml path('')

查詢結果如下：

,6,17,18,20,21,22,23,24,29,25,26,28,27

(13 行受影響)

--現在想進一步去掉左邊逗號,同時加上左右圓括號

--查詢分組號為6的子孫子節點

with 
	RTD1 as(
	select id ,pid from UserGroup
	),
	RTD2 as(
	select * from RTD1 where id=6
	union all
	select RTD1.* from RTD2 inner join RTD1 
	on RTD2.id=RTD1.PID
	)
select '('+
	STUFF(
	(select ','+cast(id as nvarchar) from RTD2 for xml path('')),1,1,''
	)+
')'

查詢結果如下：

(6,17,18,20,21,22,23,24,29,25,26,28,27)

(1 行受影響)

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

關于以上文章中關于for xml path的用法,我貼出一位朋友的博客如下[對這位朋友表示感謝]：

FOR XML PATH 有的人可能知道有的人可能不知道，其實它就是將查詢結果集以XML形式展現，有了它我們可以簡化我們的查詢語句實現一些以前可能需要借助函數活存儲過程來完成的工作。那么以一個實例為主.

一.FOR XML PATH 簡單介紹

那么還是首先來介紹一下FOR XML PATH ，假設現在有一張興趣愛好表（hobby）用來存放興趣愛好，表結構如下：

接下來我們來看應用FOR XML PATH的查詢結果語句如下：

SELECT * FROM @hobby FOR XML PATH

結果：

1
爬山


2
游泳


3
美食

由此可見FOR XML PATH 可以將查詢結果根據行輸出成XML各式！

那么，如何改變XML行節點的名稱呢？代碼如下：

SELECT * FROM @hobby FOR XML PATH('MyHobby')

結果一定也可想而知了吧？沒錯原來的行節點 變成了我們在PATH后面括號()中，自定義的名稱,結果如下：

1
爬山


2
游泳


3
美食

這個時候細心的朋友一定又會問那么列節點如何改變呢？還記的給列起別名的關鍵字AS嗎？對了就是用它!代碼如下：

SELECT hobbyID as 'MyCode',hName as 'MyName' FROM @hobby FOR XML PATH('MyHobby')

那么這個時候我們列的節點名稱也會編程我們自定義的名稱 與結果如下：

1
爬山


2
游泳


3
美食

噢！ 既然行的節點與列的節點我們都可以自定義，我們是否可以構建我們喜歡的輸出方式呢？還是看代碼：

SELECT '[ '+hName+' ]' FROM @hobby FOR XML PATH('')

沒錯我們還可以通過符號+號，來對字符串類型字段的輸出格式進行定義。結果如下：

[ 爬山 ][ 游泳 ][ 美食 ]

那么其他類型的列怎么自定義？ 沒關系，我們將它們轉換成字符串類型就行啦！例如：

SELECT '{'+STR(hobbyID)+'}','[ '+hName+' ]' FROM @hobby FOR XML PATH('')

好的 FOR XML PATH就基本介紹到這里吧，更多關于FOR XML的知識請查閱幫助文檔！

接下來我們來看一個FOR XML PATH的應用場景吧！那么開始吧。。。

二.一個應用場景與FOR XML PATH應用

首先呢！我們在增加一張學生表，列分別為（stuID,sName,hobby）,stuID代表學生編號，sName代表學生姓名，hobby列存學生的愛好！那么現在表結構如下：

這時，我們的要求是查詢學生表，顯示所有學生的愛好的結果集，代碼如下：

SELECT B.sName,LEFT(StuList,LEN(StuList)-1) as hobby FROM (
SELECT sName,
(SELECT hobby+',' FROM student
WHERE sName=A.sName
FOR XML PATH('')) AS StuList
FROM student A
GROUP BY sName
) B

結果如下:

分析： 好的，那么我們來分析一下，首先看這句：

SELECT hobby+',' FROM student
WHERE sName=A.sName
FOR XML PATH('')

這句是通過FOR XML PATH 將某一姓名如張三的愛好，顯示成格式為：“ 愛好1，愛好2，愛好3，”的格式！

那么接著看：

SELECT B.sName,LEFT(StuList,LEN(StuList)-1) as hobby FROM (
SELECT sName,
(SELECT hobby+',' FROM student
WHERE sName=A.sName
FOR XML PATH('')) AS StuList
FROM student A
GROUP BY sName
) B

剩下的代碼首先是將表分組，在執行FOR XML PATH 格式化，這時當還沒有執行最外層的SELECT時查詢出的結構為:

可以看到StuList列里面的數據都會多出一個逗號，這時隨外層的語句:SELECT B.sName,LEFT(StuList,LEN(StuList)-1) as hobby 就是來去掉逗號，并賦予有意義的列明！

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

關于stuff函數的用法：

刪除指定長度的字符串并在指定的起始點插入另一組字符
STUFF ( character_expression , start , length , character_expression )
character_expression ：操作的字符，
start:刪除和插入的起始點，
length:刪除的長度，
character_expression ：要插入的字符

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com