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.