Change dataset from multiple columns to multiple rows

Question

Post reply

Change dataset from multiple columns to multiple rows

LeDeau

SSC-Addicted

Points: 466
More actions
September 20, 2010 at 9:31 am

#240656

I have a large set of numbers in a list, but not in a table. Is it possible to use a SELECT statement to get a dataset with a single column of many rows?
Example:
SELECT 1, 2, 3, 4, 5
I'd like it to appear as:
1
2
3
4
5
Not:
1 | 2 | 3 | 4 | 5
Thanks in advance for any help that you're able to provide.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Take a gander at the first post of this thread: http://www.sqlservercentral.com/Forums/Topic988784-391-1.aspx

LeDeau SSC-Addicted Points: 466 More actions · Answer 2

Thanks for your quick reply. I took a look and wasn't able to find a solution.

Were you referring to cross apply? I didn't think I could use that if I wasn't selecting the values from a table?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

SJLee.Consulting (9/20/2010)
I have a large set of numbers in a list, but not in a table.

What exactly do you mean, "in a list, but not in a table"? Do you mean the numbers are in a single column value as a comma-delimited string, e.g. '1,2,3,4'?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

Ok, take this:

select ValueOfCols from

(

SELECT 1 col1, 2 col2, 3 col3, 4 col4, 5 col5

) Pivot_Table

UNPIVOT

(

ValueOfCols FOR Cols IN ([col1],[col2],[col3],[col4],[col5])

) Pivot_Hanle

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

If your input is as what Chris Morris said, then this would help you

DECLARE @CSV VARCHAR(100)

SELECT @CSV = '1,2,3,4,5'

SELECT x.i.value('(./text())[1]','INT') AS IDs

FROM (

SELECT XMLList=CAST(''+REPLACE(@CSV,',','')+'' AS XML).query('.')

) a

CROSS APPLY

XMLList.nodes('i') x(i)

The above code has been discussed to an exhaustive extent in the thread link i said...

LeDeau SSC-Addicted Points: 466 More actions · Answer 6

Yes, that's exactly it, here's a small portion:

SELECT 1502280,1506687,1504669,1501368,1500801,1500861,1501163,1521193,1500650,1500788,1512239,1500139,1502181,1500537,1500321,1506314,1502755,1505270,1500400,1515167,1505350,1509675,1500196,1502599,1501572,1501226,1517844,1503169,1512954

LeDeau SSC-Addicted Points: 466 More actions · Answer 7

ColdCoffee (9/20/2010)
If your input is as what Chris Morris said, then this would help you
DECLARE @CSV VARCHAR(100)
SELECT @CSV = '1,2,3,4,5'
SELECT x.i.value('(./text())[1]','INT') AS IDs
FROM (
SELECT XMLList=CAST(''+REPLACE(@CSV,',','')+'' AS XML).query('.')
) a
CROSS APPLY
XMLList.nodes('i') x(i)

This works perfectly, but the columns aren't named, I just have the word SELECT followed by 1000+ ints seperated by commas

LeDeau SSC-Addicted Points: 466 More actions · Answer 8

Sorry, I quoted the wrong response last time, this worked perfectly, thank you very much.

I must have missed it in the link earlier, most likely because I don't quite understand it.

Thanks again for everyones help and quick responses.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 9

ColdCoffee

SSC-Dedicated

Points: 39972

September 20, 2010 at 10:38 am

#1224194

Thanks for the feedback, Lee