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.
-
3Is this really what you want to do? If not you should post something more realistic.APC– APC2012-05-09 10:11:43 +00:00Commented May 9, 2012 at 10:11
-
You might want to take a look at UNPIVOT.Nikola Markovinović– Nikola Markovinović2012-05-09 10:17:08 +00:00Commented May 9, 2012 at 10:17
Add a comment
|
2 Answers
SELECT 1
UNION
SELECT 2
UNION
SELECT 3
3 Comments
ChrisCrous
Is there no way to do it without modifying to original SELECT statement? ie SELECT converttorow(SELECT 1,2,3)
Niranjan Singh
@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
Check Convert Comma Separated Values into Rows and with oracle Convert comma separated string in rows.. this and this.. this
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 2, 2012 at 0:52
