SQL Server 中master..spt_values的應用

今天在做數據分析報表的時候遇到一個這樣的問題。

表結構如下。
部門編碼、部門名稱、部門人員ID（中間用逗號分割）

我想通過和人員表鏈接，查詢出一個新的數據集，查詢出的結果集格式如下：
人員信息（ID或者姓名）、部門編碼、部門名稱

?

以前都是通過程序遍歷拆分表字段組成新的集合字段，然后在結合SQL語句查詢出結果集，但是這個報表要求只能通過SQL語句實現，以前記得可以通過寫字段分割函數再結合游標實現。然而今天在網上無意間找到一個新的方法。用“master..spt_values”來實現，具體實現方法見下面實例1感覺這個東西太好用了。把網上的實例都整理了一下，希望各路大神批評指教，也希望大家繼續(xù)把這方面的應用貼上.

?

select?number?from?master..spt_values?with(nolock)?where?type='P'
/**解釋:master..spt_values表的字段值為P的對應number字段值是從0-2047*/?
??
??
--1.將字符串轉換為列顯示?
??
if?object_id('tb')?is?not?null?drop?table?tb?
go?
create?table?tb([編號]?varchar(3),[產品]?varchar(2),[數量]?int,[單價]?int,[金額]?int,[序列號]?varchar(8))?
insert?into?tb([編號],[產品],[數量],[單價],[金額],[序列號])?
select?'001','AA',3,5,15,'12,13,14'?union?all
select?'002','BB',8,9,13,'22,23,24'
go?
select?[編號],[產品],[數量],[單價],[金額]?
,substring([序列號],b.number,charindex(',',[序列號]+',',b.number)-b.number)?as?[序列號]?
from?tb?a?with(nolock),master..spt_values?b?with(nolock)?
where?b.number>=1?and?b.number=1?and?number=1?and?number1?
/**?
value?
-----?
朋?
友?
的?
*/?
---------?
??
??
--4.提取兩個日期之間的所有月份?
if?object_id('tb')?is?not?null?drop?table?tb?
go?
create?table?tb(id?int?identity(1,1),startDate?varchar(10),endDate?varchar(10))?
insert?into?tb(startDate,endDate)?select?'2013-01-01','2013-09-25'
go?
declare?@startDate?varchar(10)?
declare?@endDate?varchar(10)?
select?@startDate=startDate,@endDate=endDate?from?tb?with(nolock)?
select?convert(varchar(7),dateadd(mm,number,@startDate),120)?as?[月份]?
from?master..spt_values?with(nolock)?
where?type='P'?and?number>=0?
and?dateadd(mm,number,@startDate)=1?
--and?number<=datediff(dd,@date,dateadd(mm,1,@date))?--對于mssql而言該語句不試用于2013-08-31的情況，這時由于9月沒有31號，固計算出來的天數是30天?
and?number=left(@time,2)?and?b.number=1?and?number<=len(s)?
and?substring('|'+s,number,1)='|'
)select?left(ss,charindex(',',ss)-1)as?s1,substring(ss,charindex(',',ss)+1,len(ss))as?s2?from?cte?
drop?table?tb?
/**?
s1?????????????s2?
-----------?------------?
車位地址1??????車位狀況1?
車位地址2??????車位狀況2?
車位地址n??????車位狀況n?
*/