How do I convert an arbitrary list of strings to a SQL rowset?

Question

I have a simple list of strings, which may be of arbitrary length. I'd like to be able to use this list of strings as I would use a rowset. The app in question is running against SQL Server.

To be clearer, if I do SELECT 'foo', 'bar', 'baz' I get 'foo', 'bar', and 'baz' as fields in a single row. I'd like to see each one of them as a separate row.

Is there a SQL (or SQLServer-specific) function or technique that I'm missing, or am I going to have to resort to writing a function in an external scripting language?

Answer 1

I may be missing the point.... but;

SELECT 'foo'
UNION
SELECT 'bar'
UNION
SELECT 'baz'

Answer 2

Well, as a 'technique' there's

SELECT 'foo'
UNION ALL
SELECT 'bar'
UNION ALL
SELECT 'baz'

(the ALLs are to cover the case where some of your strings are the same - UNION without ALL will remove duplicates); but without knowing more about your situation it's hard to say if this is what you need...

Answer 3

If it's going to be an arbitrary length you could use a PIVOT. I've only done it a few times before, but it will do what you are asking.

http://msdn.microsoft.com/en-us/library/ms177410.aspx

That may not be the best example, but should help get you started.

Answer 4

A more reusable solution would be to create a table valued function, as documented here.