SQL 逗号分隔将一行拆成多行

有表tb, 如下:
id          value
----------- -----------
1           aa,bb
2           aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id          value
----------- --------
1           aa
1           bb
2           aaa
2           bbb
2           ccc
*/

--1. 旧的解决方法(sql server 2000)

create  table  tb(id  int ,value  varchar (30))
insert  into  tb  values (1, 'aa,bb' )
insert  into  tb  values (2, 'aaa,bbb,ccc' )

go

--方法1.使用临时表完成
SELECT  TOP  8000 id = IDENTITY( int , 1, 1)  INTO  #  FROM  syscolumns a, syscolumns b

SELECT  A.id, value =  SUBSTRING (A.[value], B.id, CHARINDEX( ',' , A.[value] +  ',' , B.id) - B.id)
FROM  tb A, # B
WHERE  SUBSTRING ( ','  + A.[value], B.id, 1) =  ','

DROP  TABLE  #

--方法2.如果数据量小，可不使用临时表
select  a.id , value =  substring (a.value , b.number , charindex( ','  , a.value +  ','  , b.number) - b.number)
from  tb a  join  master..spt_values  b
on  b.type= 'p'  and  b.number  between  1  and  len(a.value)
where  substring ( ','  + a.value , b.number , 1) =  ','

--2. 新的解决方法(sql server 2005)
create  table  tb(id  int ,value  varchar (30))
insert  into  tb  values (1, 'aa,bb' )

insert  into  tb  values (2, 'aaa,bbb,ccc' )
go

--方法1.使用xml完成
SELECT  A.id, B.value  FROM
(
  SELECT  id, [value] =  CONVERT (xml, '<root><v>'  +  REPLACE ([value],  ',' ,  '</v><v>' ) +  '</v></root>' )  FROM  tb
) A  OUTER  APPLY
(
  SELECT  value = N.v.value( '.' ,  'varchar(100)' )  FROM  A.[value].nodes( '/root/v' ) N(v)
) B

--方法2.使用CTE完成
; with  tt  as
( select  id,[value]= cast ( left ([value],charindex( ',' ,[value]+ ',' )-1)  as  nvarchar(100)),Split= cast (stuff([value]+ ',' ,1,charindex( ',' ,[value]+ ',' ), '' )  as  nvarchar(100))  from  tb
union  all
select  id,[value]= cast ( left (Split,charindex( ',' ,Split)-1)  as  nvarchar(100)),Split=  cast (stuff(Split,1,charindex( ',' ,Split), '' )  as  nvarchar(100))  from  tt  where  split> ''
)
select  id,[value]  from  tt  order  by  id  option  (MAXRECURSION 0)

DROP  TABLE  tb

/*
id          value
----------- ------------------------------
1           aa
1           bb
2           aaa
2           bbb
2           ccc