insert into t1 values( 1, 'ass,dfg,ert' )
insert into t1 values( 1, 'ass,dfg' )
insert into t1 values( 1, 'vvv' )
create function dbo.f_getstr( @id int )
returns varchar(1000)
as
begin
declare @str varchar(1000)
set @str = ''
select @str = @str + str + ',' from t1 where id = @id
return @str
end
create function dbo.f_split()
returns @v table( id int, str varchar(100), seq int )
as
begin
declare @id int
declare @str varchar(1000)
declare @pos1 int, @pos2 int
declare cur cursor for select distinct id from t1
open cur
while 1 = 1
begin
fetch next from cur into @id
if @@fetch_status <> 0 break
set @str = dbo.f_getstr(@id)
set @pos1 = 1
set @pos2 = charindex( ',', @str, @pos1 )
while @pos2 > 0
begin
if not exists( select 1 from @v where id = @id and str = substring( @str, @pos1, @pos2 - @pos1 ) )
insert into @v select @id, substring( @str, @pos1, @pos2 - @pos1 ), (select count(*) from @v where id = @id ) + 1
set @pos1 = @pos2 + 1
set @pos2 = charindex( ',', @str, @pos1 )
end
end
close cur
deallocate cur
return
end
declare @sql varchar(8000)
set @sql = 'select id'
select @sql = @sql + ',max(case seq when '''+cast(seq as varchar)+''' then str end) ['+cast(seq as varchar)+']' from (select distinct seq from dbo.f_split()) as a
select @sql = @sql+' from dbo.f_split() group by id'