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