Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

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

By Andy S,

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.

Total article views: 1303 | Views in the last 30 days: 1
Related Articles

Not Selecting All Columns in select query

Not Selecting All Columns in select query


Select statement of view stays 5 hours to return results......

Select statement of view stays 5 hours to return results......


basic select 'i think'

selecting 2 columns


The Ultimate Prospective-Index Column(s) Selectivity Analyzer

Gets single-column and cumulative-column selectivity stats and @Top largest dupe sets for each cumul...


Exclude a column from SELECT * within a View

Exclude a column from SELECT * within a View


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones