April 30, 2008 at 12:38 am
Comments posted to this topic are about the item UDF to return a multi column table of values from an input string
June 11, 2008 at 5:48 am
Here is my version:
create procedure ListToMultiColumnTable (@List Varchar(max), @ColumnCount int)
as
Begin
declare @tempList Varchar(max), @tempListInner Varchar(max), @listToInsert nvarchar(max)
Declare @ColumnNumber int,@substringCount int
set @templist=replace(@List,',',''',''')+''','''
While @templist<>''''
Begin
Set @ColumnNumber = 1
set @substringCount=0
set @tempListInner=@tempList
set @listToInsert=''
while @ColumnNumber <= @ColumnCount
begin
declare @ci int
set @ci=charindex(',',@tempListInner)
if @listToInsert<>''
set @listToInsert=@listToInsert+', '
set @listToInsert=@listToInsert+substring(@templistInner,1,@ci-1) +
' col'+convert(nvarchar,@columnnumber)
set @substringCount=@substringCount+@ci
set @tempListInner=substring(@tempListInner,
@ci+1,len(@tempListInner))
Set @ColumnNumber = @ColumnNumber + 1
end
if left(@templist,1)<>''''
begin
execute ('select '''+@listToInsert+ ' into ##tab')
Set @ColumnNumber = 1
while @ColumnNumber <= @ColumnCount
begin
execute('alter table ##tab alter column col'+@ColumnNumber+' nvarchar(max)')
Set @ColumnNumber = @ColumnNumber + 1
end
end
else
execute ('insert into ##tab
select '+@listToInsert)
set @templist=substring(@templist,@substringCount+1,len(@templist))
End
select * from ##tab
drop table ##tab
Return
End
go
--output example
declare @List varchar(1000)
set @List = '1,Tea,£1.50,2,Coffee,£1.75,3,Hot Chocolate,£2.05'
exec ListToMultiColumnTable @list,3
June 16, 2008 at 2:29 am
That solution has the advantage of allowing you to return as many columns as needed. But I don't think you can execute dynamic sql statements in a udf, which is why I didn't go down that route. I must admit I didn't like limiting the number of columns in the script, but it was all I could come up with in the time available.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply