One field per row output?

[Palmyra]

Maybe a dumb question:

I would like to output data one field per row:

Instead of

Select Last, First, Middle from Names and returning

Smith Mary Anne

I need

Smith
Mary
Anne

Possible?

 

[r937]

SELECT Last AS singlecolumn
  FROM Names 
UNION ALL
SELECT First
  FROM Names 
UNION ALL
SELECT Middle 
  FROM Names

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Palmyra]

Thanks. A few things. If the fields are not all the same datatype, it fails. Also, I would need it to sort so that all of the fields in row one were in sequence, then all of the fields in row 2, etc.

I'm getting a column of field1, field2, field3, etc.

 

[r937]

If the fields are not all the same datatype, it fails.

this should have been mentioned in your first post

Also, I would need it to sort so that all of the fields in row one were in sequence

this should have been mentioned in your first post


is there anything else you would like to add to the spec?



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Palmyra]

Sorry, I never thought of doing a union, so I didn't envision the datatype problem.

As far as the sequence, I did put that in my first post.

I think that's all I need...

Thanks.

 

[markros]

Most of the types (although not every one) can be converted to nvarchar(max). I usually use this type when solving such problems.

If you have RecordNo field in your table, just add it to the UNION ALL query and ORDER by this field.

See also problem #3 in this blog post

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/interesting-t-sql-problems

which is exactly what you're after.

PluralSight Learning Library

 

[Palmyra]

I just can't see a way to do it from the blog.

The fields sort:

First
First
First
Middle
Middle
Middle
Last
Last
Last

instead of

First
Middle
Last
first
Middle
Last
First
Middle
Last

 

[r937]

with just those columns to work with, we can't do much

could you please tell us what columns are available in the table

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[markros]

As I said, you need to have something like RecordNo or ID in your table in order to sort by this field.

PluralSight Learning Library