Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

UDF to return a multi column table of values from an input string Expand / Collapse
Author
Message
Posted Wednesday, April 30, 2008 12:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 5:09 AM
Points: 14, Visits: 77
Comments posted to this topic are about the item UDF to return a multi column table of values from an input string
Post #492670
Posted Wednesday, June 11, 2008 5:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 09, 2011 1:44 AM
Points: 291, Visits: 106
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

Post #515086
Posted Monday, June 16, 2008 2:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 5:09 AM
Points: 14, Visits: 77
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.
Post #517394
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse