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
tmajor99Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve WalesSenior Database AdministratorCommented:
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 HornMicrosoft SQL Server Data DudeCommented:
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

PortletPaulEE Topic AdvisorCommented:
Do it in one pass of the table

select
           ca.field
from table
cross apply (
  values
             (field1)
          , (field2)
          , (field3)
          , (field4)
          , (field5)
 ) ca (field)
-- & as an option
-- where ca.field is NOT NULL

if you want a unique list then use "select distinct"

& If you need a column from the table repeated on each new row

select -- distinct ???
           id, ca.field
from table
cross apply (
  values
             (field1)
          , (field2)
          , (field3)
          , (field4)
          , (field5)
 ) ca (field)
-- & as an option
-- where ca.field is NOT NULL

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jim HornMicrosoft SQL Server Data DudeCommented:
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

PortletPaulEE Topic AdvisorCommented:
:) 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 HornMicrosoft SQL Server Data DudeCommented:
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 WalesSenior Database AdministratorCommented:
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ãoMSSQL Senior EngineerCommented:
tmajor99, do you still need help with this question?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.