declare @t table(col1 varchar(10),col2 varchar(1000)) insert into @t select 'A', 'a,b,c,d'select top 100 identity(int,1,1) [ID] into # from syscolumns a,syscolumns bselect a.col1 ,col2=substring(a.col2,b.[ID],charindex(',',a.col2+',',b.[ID])-b.[ID]) from @t a join # b on substring(','+a.col2,b.ID,1)=','drop table #
if object_id('test') is not null drop table test select 'A' as col1, 'aa,bb,cc,dd' as col2 into test union select 'B', 'a,c,e' union select 'C', 'b,d,g,h' ------------------------------------------- if object_id('tempdb..#') is not null drop table # select * into # from test where 1 = 0 declare @col1 varchar(10), @col2 varchar(100) declare @n int declare testcursor cursor for select col1, col2 + ',' as col2 from test open testcursor fetch next from testcursor into @col1,@col2 while @@fetch_status = 0 begin set @n = charindex(',', @col2) while @n > 0 begin insert into # values(@col1, left(@col2, @n - 1)) set @col2 = right(@col2, len(@col2) - @n) set @n = charindex(',', @col2) end fetch next from testcursor into @col1,@col2 end close testcursor deallocate testcursor select * from # /* col1 col2 A aa A bb A cc A dd B a B c B e C b C d C g C h */ drop table # ------------------------------------------- drop table test
insert into @t
select 'A', 'a,b,c,d'select top 100 identity(int,1,1) [ID] into # from syscolumns a,syscolumns bselect a.col1
,col2=substring(a.col2,b.[ID],charindex(',',a.col2+',',b.[ID])-b.[ID])
from @t a
join # b on substring(','+a.col2,b.ID,1)=','drop table #
select 'A' as col1, 'aa,bb,cc,dd' as col2
into test
union select 'B', 'a,c,e'
union select 'C', 'b,d,g,h'
-------------------------------------------
if object_id('tempdb..#') is not null drop table #
select * into # from test where 1 = 0
declare @col1 varchar(10), @col2 varchar(100)
declare @n int
declare testcursor cursor for select col1, col2 + ',' as col2 from test
open testcursor
fetch next from testcursor into @col1,@col2
while @@fetch_status = 0
begin
set @n = charindex(',', @col2)
while @n > 0
begin
insert into # values(@col1, left(@col2, @n - 1))
set @col2 = right(@col2, len(@col2) - @n)
set @n = charindex(',', @col2)
end
fetch next from testcursor into @col1,@col2
end
close testcursor
deallocate testcursor
select * from #
/*
col1 col2
A aa
A bb
A cc
A dd
B a
B c
B e
C b
C d
C g
C h
*/
drop table #
-------------------------------------------
drop table test