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.

Is this really what you want to do? If not you should post something more realistic. — APC, May 09 '12 at 10:11
You might want to take a look at [UNPIVOT](http://msdn.microsoft.com/en-us/library/ms141723.aspx). — Nikola Markovinović, May 09 '12 at 10:17

score 3 · Accepted Answer · answered May 09 '12 at 10:07

3

SELECT 1
UNION
SELECT 2
UNION
SELECT 3

answered May 09 '12 at 10:07

aF.

Is there no way to do it without modifying to original SELECT statement? ie SELECT converttorow(SELECT 1,2,3) – ChrisCrous May 09 '12 at 11:00
1

@ChrisCrous: you can create your function or storedprocedure where you just pass these comma separated values and there you can put your t-sql logic to generate such output.. – Niranjan Singh May 09 '12 at 11:27
1

Check [Convert Comma Separated Values into Rows](http://www.sqlservercentral.com/Forums/Topic1043869-149-1.aspx#bm1043876) and with oracle [Convert comma separated string in rows](https://forums.oracle.com/forums/thread.jspa?threadID=2320358).. [this](http://www.sqlservercentral.com/Forums/Topic968028-338-1.aspx#bm968044) and [this](http://stackoverflow.com/questions/887628/convert-multiple-rows-into-one-with-comma-as-separator).. [this](http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows) – Niranjan Singh May 09 '12 at 11:43

score 0 · Answer 2 · answered Aug 02 '12 at 00:52

0

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)

answered Aug 02 '12 at 00:52

Nathan Griffiths

2 Answers2