今天在做數(shù)據(jù)分析報(bào)表的時(shí)候遇到一個(gè)這樣的問題。

表結(jié)構(gòu)如下。部門編碼、部門名稱、部門人員ID（中間用逗號(hào)分割）

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

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

select number from master..spt_values with(nolock) where type='P'/**解釋:master..spt_values表的字段值為P的對(duì)應(yīng)number字段值是從0-2047*/     --1.將字符串轉(zhuǎn)換為列顯示   if object_id('tb') is not null drop table tb go create table tb([編號(hào)] varchar(3),[產(chǎn)品] varchar(2),[數(shù)量] int,[單價(jià)] int,[金額] int,[序列號(hào)] varchar(8)) insert into tb([編號(hào)],[產(chǎn)品],[數(shù)量],[單價(jià)],[金額],[序列號(hào)]) select '001','AA',3,5,15,'12,13,14' union allselect '002','BB',8,9,13,'22,23,24'go select [編號(hào)],[產(chǎn)品],[數(shù)量],[單價(jià)],[金額] ,substring([序列號(hào)],b.number,charindex(',',[序列號(hào)]+',',b.number)-b.number) as [序列號(hào)] from tb a with(nolock),master..spt_values b with(nolock) where b.number>=1 and b.number<len(a.[序列號(hào)]) and b.type='P'and substring(','+[序列號(hào)],number,1)=','go drop table tb go /** 編號(hào)   產(chǎn)品   數(shù)量          單價(jià)          金額          序列號(hào) ---- ---- ----------- ----------- ----------- -------- 001  AA   3           5           15          12 001  AA   3           5           15          13 001  AA   3           5           15          14 002  BB   8           9           13          22 002  BB   8           9           13          23 002  BB   8           9           13          24 */ ----------   --2.第四個(gè)逗號(hào)之前的字符串 declare @str varchar(100) set @str='10,102,10254,103265,541,2154,41,156';with cte as( select left(@str,number-1) as ss,row_number()over(order by getdate()) as xh from master..spt_values with(nolock)  where number>=1 and number<=len(@str+',') and type='P' and substring(@str+',',number,1)=',')select ss from cte where xh=4 /** ss ------------------- 10,102,10254,103265 */ ----------     --3.找出兩句話中相同的漢字 declare @Lctext1 varchar(100) declare @Lctext2 varchar(100) set @Lctext1='我們都是來自五湖四海的朋友'set @Lctext2='朋友多了路真的好走嗎'select substring(@Lctext2,number,1) as value from master..spt_values with(nolock) where type='P' and number>=1 and number<=len(@Lctext2) and charindex(substring(@Lctext2,number,1),@Lctext1,number)>1 /** value ----- 朋 友 的 */ ---------     --4.提取兩個(gè)日期之間的所有月份 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)<=@endDate go drop table tb go /** 月份 ------- 2013-01 2013-02 2013-03 2013-04 2013-05 2013-06 2013-07 2013-08 2013-09 */ ---------     --5.求一個(gè)日期所在月份的所有日期 declare @date datetime set @date='2013-08-31'select convert(char(7),@date,120)+'-'+right('0'+convert(varchar(2),number),2) as [日期格式1] ,ltrim(year(@date))+right(100+month(@date),2)+right('0'+ltrim(number),2) as [日期格式2] from master..spt_values with(nolock) where type='P' and number>=1 --and number<=datediff(dd,@date,dateadd(mm,1,@date)) --對(duì)于mssql而言該語句不試用于2013-08-31的情況，這時(shí)由于9月沒有31號(hào)，固計(jì)算出來的天數(shù)是30天 and number<=datediff(dd,convert(char(7),@date,120)+'-01',convert(char(7),dateadd(mm,1,@date),120)+'-01')--轉(zhuǎn)換為1號(hào)來計(jì)算天數(shù) /** 日期格式1       日期格式2 ----------- -------------------- 2013-08-01  20130801 2013-08-02  20130802 2013-08-03  20130803 2013-08-04  20130804 2013-08-05  20130805 2013-08-06  20130806 2013-08-07  20130807 2013-08-08  20130808 2013-08-09  20130809 2013-08-10  20130810 2013-08-11  20130811 2013-08-12  20130812 2013-08-13  20130813 2013-08-14  20130814 2013-08-15  20130815 2013-08-16  20130816 2013-08-17  20130817 2013-08-18  20130818 2013-08-19  20130819 2013-08-20  20130820 2013-08-21  20130821 2013-08-22  20130822 2013-08-23  20130823 2013-08-24  20130824 2013-08-25  20130825 2013-08-26  20130826 2013-08-27  20130827 2013-08-28  20130828 2013-08-29  20130829 2013-08-30  20130830 2013-08-31  20130831 */ ---------     --6.根據(jù)給定時(shí)間為基準(zhǔn)以2小時(shí)為劃分，得出一天劃分出的時(shí)間段 declare @time varchar(5) set @time='11:13'select ltrim(a.number)+right(@time,3)+'-'+ltrim(b.number)+right(@time,3) as [劃分結(jié)果] from master..spt_values a with(nolock),master..spt_values b with(nolock) where a.type='P' and b.type='P'and a.number>=left(@time,2) and b.number<=24 and a.number+2=b.number /** 劃分結(jié)果 ----------------------------------- 11:13-13:13 12:13-14:13 13:13-15:13 14:13-16:13 15:13-17:13 16:13-18:13 17:13-19:13 18:13-20:13 19:13-21:13 20:13-22:13 21:13-23:13 22:13-24:13 */ ---------     --7.將字符串顯示為行列 if object_id('tb') is not null drop table tb create table tb(id int identity(1,1),s nvarchar(100)) insert into tb(s) select '車位地址1,車位狀況1|車位地址2,車位狀況2|車位地址n,車位狀況n';with cte as( select substring(s,number,charindex('|',s+'|',number)-number) as ss from tb with(nolock),master..spt_values with(nolock) where type='P' and 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 */