|
--说明:例如,将下列数据 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 |