--说明:例如,将下列数据

id id_value

-----------------

1 'aa,bb'

2 'aa,bb,cc'

3 'aaa,bbb,ccc'

--转换成以下的格式

id id_value

-----------------

1 'aa'

1 'bb'

2 'aa'

2 'bb'

2 'cc'

3 'aaa'

3 'bbb'

3 'ccc'

--代码-------------------------------------------GO

复制代码 代码如下:

create table ta

(

id int,

id_value varchar(30)

)

insert into ta

select 1,'aa,bb' union all

select 2,'aa,bb,cc' union all

select 3,'aaa,bbb,ccc'

select

top 4000 ix = identity(int, 1, 1)

into #tb

from sys.objects a, sys.objects b

select

id,

id_value = substring(id_value,ix,charindex(',',id_value + ',',ix) - ix)

FROM ta as A, #tb as B

WHERE SUBSTRING( ',' + id_value, ix, 1) = ','

drop table ta

drop table #tb