asked on

SQL Select Help

Just need a simple MS SQL Select that will combine all columns from a table into one column. For example;

Field1        Field2 Field3 Field4 Field5
1897019            4780819        3619506      3908610        4408630
4297045            4780811        3619500      3908611        4408631

I just want a SQL Select that will combine all these fields into one like this:

Field
1897019
4297045
4780819
4780811
3619506
3619500
3908610
3908611
4408630
4408631

Steve Wales

Simple way might be:

select Field1
from Table
UNION ALL
select Field2
from Table
UNION ALL
select Field3
from Table
UNION ALL
select Field4
from Table
UNION ALL
select Field5
from Table

Open in new window

If you don't want duplicates, replace UNION ALL with UNION

Jim Horn

For starters, assuming order does not mater for now, UNION ALL the columns together..

SELECT Field1 as Field FROM YourTable
UNION ALL 
SELECT Field2 FROM YourTable
UNION ALL 
SELECT Field3 FROM YourTable
UNION ALL 
SELECT Field4 FROM YourTable
UNION ALL 
SELECT Field5 FROM YourTable

Open in new window

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Horn

Both (Mine and Steve's) and Paul's solutions worked, but when I ran in SSMS and looked at the execution plan stats Paul's ran 5x faster, as Paul pointed out it does only one table scan instead of five.
Learn something new every day.

CREATE TABLE #tmp (Field1 int, Field2 int, Field3 int, Field4 int, Field5 int) 

INSERT INTO #tmp (Field1, Field2, Field3, Field4, Field5) 
VALUES 
(1897019, 4780819, 3619506, 3908610, 4408630),
(4297045, 4780811, 3619500, 3908611, 4408631)


SELECT Field1 as Field FROM #tmp
UNION ALL 
SELECT Field2 FROM #tmp
UNION ALL 
SELECT Field3 FROM #tmp
UNION ALL 
SELECT Field4 FROM #tmp
UNION ALL 
SELECT Field5 FROM #tmp

select ca.field
from #tmp
   cross apply (values (field1), (field2), (field3), (field4), (field5)) ca (field)

Open in new window

PortletPaul

:) thanks Jim

Spotlight on UNPIVOT, Part 1
http://bradsruminations.blogspot.com.au/2010/02/spotlight-on-unpivot-part-1.html

an oldie but a goodie.

One could use UNPIVOT for this question too, however if you read that blog Brad Schultz compares the UNPIVOT to APPLY and comes up with some interesting conclusions. Basically I like using the CROSS APPLY & VALUES because the syntax seems so initiative as you almost get a WYSIWYG layout. (well I think so)

Cheers.
& it's also a balmy beautiful spring day down here; 23c rising to 27c

Jim Horn

Currently at PASS Summit in Seattle, WA. 55F degrees every morning. Microsoft is throwing a big appreciation party for us at the Experience Music Project (aka rock and roll museum) tonight.

I heard Minnesota had its first heavy snow yesterday.

Steve Wales

I kinda figured that the UNION solution would be a 5X scan.

However - it's much more intuitive code to read than the cross apply (to me).

That said, doesn't mean I am not putting that one in my bag of tricks for future use. Thanks for the tip, Paul :)

(It will be 23 to 27 degrees here too, soon enough - them funny "F" degrees we use here in the US)

Vitor Montalvão

tmajor99, do you still need help with this question?