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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy