请问这个SQL如何写？

Question

有表tb, 如下:
id          values
----------- -----------
1           aa,bb
2           aaa,bbb,ccc欲按,分拆values列, 分拆后结果如下:
id          value
----------- --------
1           aa
1           bb
2           aaa
2           bbb
2           ccc之前的老办法, 一般是使用临时表
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b
SELECT A.id, SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[values], B.id, 1) = ','
DROP TABLE #
在sql 2005中, 借用xml, 可以简单地一句来实现这个功能-- 示例数据
DECLARE @t TABLE(id int, [values] varchar(100))
INSERT @t SELECT 1, 'aa,bb'
UNION ALL SELECT 2, 'aaa,bbb,ccc'-- 查询处理
SELECT A.id, B.value
FROM(
SELECT id, [values] = CONVERT(xml,
'<root><v>' + REPLACE([values], ',', '</v><v>') + '</v></root>')
FROM @t
)A
OUTER APPLY(
SELECT value = N.v.value('.', 'varchar(100)')
FROM A.[values].nodes('/root/v') N(v)
)B/*--结果
id          value
----------- --------
1           aa
1           bb

Answer 1

/*
表的结构:
year_month           card_id             rest_days 2004-01              0100001             01,07,09,14
2004-01              0100002             01,07,12,21
2004-01              0100003             01,04,13,29
....................................................
2004_04              0100001             01,07,09,14
2004_04              0100002             12,07,12,21
2004_04              0100003             21,04,13,29
*/
declare @a table(year_month varchar(10),card_id varchar(10),rest_days varchar(20))insert @a select '2004-01','0100001','01,07,09,14'
union all select '2004-01','0100002','01,07,12,21'
union all select '2004-01','0100003','01,04,13,29'
union all select '2004_04','0100001','01,07,09,14'
union all select '2004_04','0100002','12,07,12,21'
union all select '2004_04','0100003','21,04,13,29'select card_id,year_month+'-'+substring(rest_days,N,charindex(',',rest_days +',',N)-N) as d
from @a a,
(select 1 as N
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
union all select 10
union all select 11) b 
where substring(','+rest_days,N,1)= ',' 
order by card_id,N
/*
card_id    d                               
---------- ------------------------------- 
0100001    2004-01-01
0100001    2004_04-01
0100001    2004_04-07
0100001    2004-01-07
0100001    2004-01-09
0100001    2004_04-09
0100001    2004_04-14
0100001    2004-01-14
0100002    2004-01-01
0100002    2004_04-12
0100002    2004_04-07
0100002    2004-01-07
0100002    2004-01-12
0100002    2004_04-12
0100002    2004_04-21
0100002    2004-01-21
0100003    2004-01-01
0100003    2004_04-21
0100003    2004_04-04
0100003    2004-01-04
0100003    2004-01-13
0100003    2004_04-13
0100003    2004_04-29
0100003    2004-01-29（所影响的行数为 24 行）*/

Answer 2

if not object_id('Tab') is null
    drop table Tab
Go
Create table Tab(FID int,F1 varchar(20),F2 varchar(20))
Insert Tab select 1,'N1;N22;N234','XXX' 
union all select 2,'B2;BT;BMM','YY'
Go
if object_id('Tempdb..#Num') is not null
    drop table #Num
go
select top 100 ID=Identity(int,1,1) into #Num from syscolumns
Select 
    a.FID,F1=substring(a.F1,b.ID,charindex(';',a.F1+';',b.ID)-b.ID),a.F2 
from 
    Tab a,#Num b
where
    charindex(';',';'+a.F1,b.ID)=b.ID /*FID         F1                   F2                   
----------- -------------------- -------------------- 
1           N1                   XXX
1           N22                  XXX
1           N234                 XXX
2           B2                   YY
2           BT                   YY
2           BMM                  YY（所影响的行数为 6 行）
*/

Answer 3

------------------------------------
-- Author:  happyflsytone  
-- Version:V1.001  
-- Date:2008-09-22 13:43:22
-------------------------------------- Test Data: ta
If object_id('ta') is not null 
    Drop table ta
Create table ta(FID int,F1 nvarchar(11),F2 nvarchar(3))
Go
Insert into ta
select 1,'N1;N22;N234','XXX' union all
select 2,'B2;BT;BMM','YYY' 
Go
--Start
select fid,substring(f1,N,charindex(';',f1 +';',N)-N) as f1,f2
from ta a,
(select 1 as N
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
union all select 10
union all select 11) b 
where substring(';'+f1,N,1)= ';' 
order by fid,N
--Result:
/*
fid         f1          f2
----------- ----------- ----
1           N1          XXX
1           N22         XXX
1           N234        XXX
2           B2          YYY
2           BT          YYY
2           BMM         YYY(6 行受影响)
*/
--End 

Answer 4

SELECT A.Fid, B.F1,A.F2
FROM(
SELECT Fid,F2, [F1] = CONVERT(xml,
'<root><v>' + REPLACE([F1], ';', '</v><v>') + '</v></root>')
FROM ta
)A
OUTER APPLY(
SELECT F1 = N.v.value('.', 'varchar(100)')
FROM A.[F1].nodes('/root/v') N(v)
)B