How to combine multiple columns into one column

[JaEzell]

Ok, here's the setup. One table has columns, Account_Number1, Account_Number2, Account_Number3, Account_Number4, Account_Number5, and Account_Number6. Some of customers will only have 1 Account_Number, but some may have up to six. I need either query a query that will display them all in a one-column list. OR if I need to setup a separate table to keep a total list of accounts I could. Any ideas?

 

[cchitanu]

Why not using a stored procedure ?

 

[tlbroadbent]

It seems that redesign is in order. You should have a table with a single row for customer accounts.

You can get the result you want from the current table with a UNION query.

Select
CustomerID, AccountNo=Account1
From AccountsTbl
Where Account1 Is Not Null

UNION ALL

Select
CustomerID, AccountNo=Account2
From AccountsTbl
Where Account2 Is Not Null

UNION ALL

Select
CustomerID, AccountNo=Account3
From AccountsTbl
Where Account3 Is Not Null

UNION ALL

.
.
. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.