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

,

This udf takes 2 input parameters: a string of comma separated values, and the number of columns to return (up to 5 columns).

I had a situation where I needed to use a stored procedure to return a table of values. These values were financial sectors and the recommended percentage of a client's portfolio in each sector. These values change slightly depending on certain conditions (eg the client's age, attitude to risk), but are unlikely to change over time.

I thought about using a table of possible values and querying this, or building up a hard coded list of values using the union operator. However, these options would either result in a lot of data to maintain, or a lengthy series of Select ... union statements. So instead I wrote a function which would return an n column table based on a string input.

This allowed me to quickly generate select statements using eg:

select * from dbo.ListToMultiColumnTable('Cash,20,Fixed Interest,20,Property,30,Equities,30,Fixed Equities,0', 2)

You could also use this udf to insert, eg

Insert into MyTable (MyTableID, Column1)

select * from dbo.ListToMultiColumnTable('1,Red,2,Orange,3,Yellow,4,Green,5,Blue,6,Indigo,7,Violet', 2)

Hope this is of some use to others.

CREATE Function ListToMultiColumnTable ( @List Varchar(8000), @ColumnCount int)
Returns @Table table
(
	Column1 varchar(255),
	Column2 varchar(255),
	Column3 varchar(255),
	Column4 varchar(255),
	Column5 varchar(255))
as
Begin

Declare @Value varchar(255)
Declare @Column1 varchar(255)
Declare @Column2 varchar(255)
Declare @Column3 varchar(255)
Declare @Column4 varchar(255)
Declare @Column5 varchar(255)
Declare @ColumnNumber int

While Len(@List) > 0
	Begin
	Set @Column1 = ''
	Set @Column2 = ''
	Set @Column3 = ''
	Set @Column4 = ''
	Set @Column5 = ''
	Set @ColumnNumber = 1

	while @ColumnNumber <= @ColumnCount
		begin
		set @Value = ''
		While Left(@List,1) <> ',' and Len(@List) > 0
			Begin
			Set @Value = @Value + Left(@List,1)
			Set @List = Right(@List,Len(@List) - 1)
			End

		Set @Column1 = case when @ColumnNumber = 1 then @Value else @Column1 endSet @Column2 = case when @ColumnNumber = 2 then @Value else @Column2 endSet @Column3 = case when @ColumnNumber = 3 then @Value else @Column3 endSet @Column4 = case when @ColumnNumber = 4 then @Value else @Column4 endSet @Column5 = case when @ColumnNumber = 5 then @Value else @Column5 end
		Set @ColumnNumber = @ColumnNumber + 1
		If Len(@List) > 0
			Begin
			Set @List = Right(@List,Len(@List) - 1)
			End
		end
	
	Insert @Table Values(@Column1, @Column2, @Column3, @Column4, @Column5)
	End
Return
End

--output example

declare @List varchar(1000)
set @List = '1,Tea,£1.50,2,Coffee,£1.75,3,Hot Chocolate,£2.05'
select * from dbo.ListToMultiColumnTable(@List, 3)

Rate

3.33 (3)

Share

Share

Rate

3.33 (3)