Sql Server : 一欄位轉成多筆資料列

2010-09-06

26869
0
Sql

日前工作上遇到了個需求，要將一欄位的內容轉成多筆資料，以利後續資料彙整，舉例來說，有一 Books Table

Id	Name	Category
1	哈利波特	奇幻,外文,魔法
2	神雕俠侶	武俠,現代
3	西遊記	奇幻,古文
4	三國演義	古文,歷史,謀略
5	老人與海	外文
6	海賊王	漫畫,現代
…	…	…

目的是將其轉換成如下

Id	Name	Category
1	哈利波特	奇幻
1	哈利波特	外文
1	哈利波特	魔法
2	神雕俠侶	武俠
2	神雕俠侶	現代
3	西遊記	奇幻
3	西遊記	古文
4	三國演義	古文
4	三國演義	歷史
4	三國演義	謀略
…	…	…
略…

如果說今天是反過來，多筆資料合併一欄位，許多書本與網路上都有相關解法，比較乾淨簡潔的方式是利用For Xml Path 與Stuff 即可達成，詳細作法google 一下就可以查到許多

但一筆資料分割轉成多筆資料列，雖說也有一些辦法，但多半用charindex 算位置而後分割儲存到Table內

不過想說既然 xml 方式可以簡潔的將多筆記錄轉合併成一筆，那應該也可以一筆記錄分割成多筆資料，試了一會，找到了比較可以接受的簡潔方式，作法如下

1.Create 一個 function, return table，而過程是將Category 欄位先轉成xml variable，再運用nodes() method 轉成多筆資料


Create Function fnConvertXmlToTable(@ID Int)
Returns @Table Table(Category Nvarchar(10))
As
Begin
	Declare @Xml xml;
	--將逗號 Replace 成</Category><Category>，最前面加入<Category>及最後面再加入</Category>
	Select Top 1 @Xml =Cast('<Category>' + Replace(Category , ',', '</Category><Category>') + '</Category>' As XML)
	From dbo.Books
	Where ID = @ID
	
	Insert Into @Table(Category)
	Select col.value('.', 'nvarchar(10)')
	From @Xml.nodes('/Category') Doc(col)
	
	Return
End

2. 運用cross apply


Select a.ID, a.Name, b.Category
From Books a
	Cross Apply dbo.fnConvertXmlToTable(a.ID) b
Order By a.ID

如此一來結果產出，Sql script 也乾淨簡潔多了

回首頁