Convert SQL columns to rows

Question

I would like to split one records columns into multiple rows.

If I have a SQL statement like the following:

SELECT 1,2,3

Result:

1 | 2 | 3

How do I convert that to the following result?

1
2
3

I am currently using MS SQL 2008.

Answer 1

To sum up the comments above:

• If you want to convert columns into rows, you will need to use the T-SQL UNPIVOT clause.

  If you want to split a single column that contains comma separated values, you will need to create a Function (example here)

Answer 2

SELECT 1
UNION
SELECT 2
UNION
SELECT 3