Thanks Frank and Dave. Sorry for the delay, I got pulled into something else. I was able to get to where I needed to go from your input. Here's what I came up with.
CREATE procedure FTW
@jobid int
as
declare @max int
declare @maxouter int
declare @ct int
declare @gl int
declare @sql nvarchar(4000)
declare @pstring nvarchar(255)
--get max # of columns and create table with that many columns
select @max = max(ColPosition) from frtabledef where jobid = @jobid
create table #newtable (RowNum int identity (1,1) primary key, Jobid varchar(50))
set @ct=0
while (@ct < @max)
begin
set @ct=@ct+1
set @sql = 'alter table #newtable add Col'+
cast(@ct as varchar)+
' varchar(255) null'
exec sp_executesql @sql
end
--begin outer loop for amount of rows
set @maxouter = (select GroupLevels from frtableinfo where jobid = @jobid)
set @gl =0
while (@gl < @maxouter)
begin
set @gl=@gl+1
insert into #newtable (Jobid) values (@Jobid)
set @ct=0
--begin inner loop to populate columns
while (@ct < @max)
begin
set @ct=@ct+1
set @pstring = dbo.ufn_parsestring((select ColName from frtabledef where jobid = @jobid and ColPosition = @ct),'|',@gl)
--print @pstring
set @sql = 'update a set a.Col'+
cast(@ct as varchar)+
' = '''+@pstring+
''' from #newtable a inner join frtabledef b on b.Jobid = a.jobid and b.ColPosition = '+
cast(@ct as varchar)+
' where a.Jobid = '+
cast(@Jobid as varchar)+' and a.RowNum = '+cast(@gl as varchar)
exec sp_executesql @sql
--print @sql
end
end
select * from #newtable
GO
and here's the function I used to parse the string.
CREATE function ufn_parsestring (
@string nvarchar(255),
@delimiter nchar(1),
@value int)
returns nvarchar(255)
as
begin
declare @index int
declare @charpos int
declare @startpos int
set @index = 0
set @charpos = 1
set @startpos = 0
set @string = @string + @delimiter
while @index < @value - 1 and @charpos <= len(@string)
begin
set @charpos = @charpos + 1
if substring(@string,@charpos,1) = @delimiter
begin
set @index = @index + 1
end
end
if @charpos > len(@string)
begin
return NULL
end
if @value = 1
set @startpos = -1
else
set @startpos = @charpos + 1
set @charpos = @startpos
while substring(@string,@charpos,1) <> @delimiter and @charpos <= len(@string)
begin
set @charpos = @charpos + 1
end
if @charpos > len(@string)
begin
return NULL
end
return ltrim(substring(@string,@startpos,(@charpos) - @startpos))
end
Thanks again,
Richard.