--示例数据create table tb(id int,name varchar(8000)) insert tb select 1,',1,3,4,' union all select 2,',12,34,67,89,' go--转换处理 select top 8000 id=identity(int) into # from syscolumns a,syscolumns b select a.id,name=substring(a.name,b.id,charindex(',',a.name+',',b.id)-b.id) from tb a,# b where len(a.name)>b.id and substring(','+a.name,b.id,1)=',' and substring(a.name,b.id,1)<>',' order by a.id,b.id drop table # go--删除测试 drop table tb/*--结果id name ----------- ------------- 1 1 1 3 1 4 2 12 2 34 2 67 2 89(所影响的行数为 7 行) --*/
declare @a table (id int,n nvarchar(100)) insert into @a select 1,',1,3,4,' insert into @a select 2,',12,34,67,89,'declare @s nvarchar(4000) set @s='' select @s=@s+replace(left(n,len(n)-1),',',' union all select '+quotename(id,'''')+',') from @a set @s=stuff(@s,1,11,'') print @s exec(@s)
这不是sql的问题,
哎,这么样的设计很糟糕的,既浪费存储空间又浪费执行时间。
设计都这样了~~没办法~~~
id name
1 ,1,3,4,
2 ,12,34,67,89,
id name
1 ,1,3,4,
2 ,12,34,67,89,
insert tb select 1,',1,3,4,'
union all select 2,',12,34,67,89,'
go--转换处理
select top 8000 id=identity(int) into # from syscolumns a,syscolumns b
select a.id,name=substring(a.name,b.id,charindex(',',a.name+',',b.id)-b.id)
from tb a,# b
where len(a.name)>b.id
and substring(','+a.name,b.id,1)=','
and substring(a.name,b.id,1)<>','
order by a.id,b.id
drop table #
go--删除测试
drop table tb/*--结果id name
----------- -------------
1 1
1 3
1 4
2 12
2 34
2 67
2 89(所影响的行数为 7 行)
--*/
insert into @a select 1,',1,3,4,'
insert into @a select 2,',12,34,67,89,'declare @s nvarchar(4000)
set @s=''
select @s=@s+replace(left(n,len(n)-1),',',' union all select '+quotename(id,'''')+',') from @a
set @s=stuff(@s,1,11,'')
print @s
exec(@s)