UDF to return a multi column table of values from an input string

  • Andy Siantonas

    SSC Enthusiast

    Points: 118

    Comments posted to this topic are about the item UDF to return a multi column table of values from an input string

  • Gargamel

    Old Hand

    Points: 315

    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

  • Andy Siantonas

    SSC Enthusiast

    Points: 118

    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 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply