sql语句，如何一行转多行

例如：
数据表table1 中有如下表结构和数据
字段 code name remark
     001  名称  1,2,3


如何通过一个sql语句，可以搜索出如下
001 名称  1
001 名称  2
001 名称  3

意思就是通过对一个字段的内容拆分，把一行数据转换成多行。

5 个解决方案

#1

--分拆列值
--原著：邹建
--改编：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)  2007-12-16  广东深圳

/*
有表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) 
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) 

create table tb(id int,value varchar(30)) 
insert into tb values(1,'aa,bb') 
insert into tb values(2,'aaa,bbb,ccc') 
go 
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 

(5 行受影响) 
*/

#2

select a.code,a.name,substring(a.remark,b.number,charindex(',',b.remark+',',b.number)-b.number)remark
from tb a,master..spt_values b
where b.[type] = 'p' and b.number between 1 and len(a.remark)
   and substring(','a.remark,b.number,1) = ','

#3

select
 a.code,a.name,substring(a.remark,b.number,charindex(',',b.remark+',',b.number)-b.number)remark
from
 tb a,master..spt_values b
where
 b.[type] = 'p' and b.number between 1 and len(a.remark)
and
 substring(','a.remark,b.number,1) = ','

#4

use SSISTest;
go
Set nocount on 
if object_id('tb') is not null
drop table tb
go
create table tb
(
 [code] nvarchar(4),
[name] nvarchar(4),
[value] nvarchar(10)
)
insert tb
select '001','张三','1,2,3,4' union all
select '002','李四','a,s,d,f,g'
go
SELECT 
a.[code],
a.[name],b.[valueb]
FROM (SELECT [code],[name],[value]=CAST('<v>'+REPLACE([value],',','</v><v>')+'</v>' AS xml) FROM tb) a
OUTER APPLY (
SELECT [valueb]=C.value('.','varchar(50)') 
FROM 
a.[value].nodes('/v') AS T(C)
) b

select * from tb
/*
code name value
---- ---- ----------
001  张三   1,2,3,4
002  李四   a,s,d,f,g


转为


code name valueb
---- ---- -------
001  张三   1
001  张三   2
001  张三   3
001  张三   4
002  李四   a
002  李四   s
002  李四   d
002  李四   f
002  李四   g
*/

#5

-- 学习楼上的..学习了。
declare @t table(code varchar(10),value varchar(100));
insert into @t select 'A','1,4,5,6,3' union all 
select 'B','3,5,7,8,1';
select * from @t;

select a.code,b.vv from 
(select code,value=cast('<v>'+REPLACE(value,',','</v><v>')+'</v>' as xml) from @t) a
outer apply(
 select vv=t.c.value('.','int') from a.value.nodes('v') t(c)
) b