分拆列值

分拆列值

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

创建表和测试数据

?

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. 旧的解决方法(sql server 2000)

?

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  #

　　2. 新的解决方法(sql server 2005)

?

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    DROP  TABLE  tb

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