Solved

# How to list values from two columns in one column

Posted on 2010-09-21
244 Views
I am trying to combine the values from two columns (A and B) into one custom field (C).  I don't want anything fancy to take place.  Just need column C to list values of A followed by the values of B.  Just one long list.

Does anyone know the SQL function for this.  I thought there was a LIST() of sometype.
0

LVL 1

Expert Comment

ID: 33728871
SELECT CONCAT(A,B) FROM table
Or with a space in between
SELECT CONCAT(A,' ',B) FROM table
0

LVL 1

Expert Comment

ID: 33728872
What are the field types for columns A and B?
0

LVL 8

Expert Comment

ID: 33728884
do you use the concat function yet?

SELECT CONCAT(col1,col2) FROM table1
0

LVL 58

Expert Comment

ID: 33728912
If you want each row's A and B to appear in C, then

select A + ' ' + B AS C
from tbl

If you want A to appear, then B to appear "A followed by the values of B" then (this assumes a and B are both varchar)

select C
from
(
select part='A', A
from tbl
union all
select part='B', B
from tbl
) X
order by part
0

Author Comment

ID: 33728994
The problem with the CONCAT() function is it combines the values of A and B into one cell.  I just want a list of the values in A (say 1-25) then followed by a list of values in B (26-35).  Is there any function that does this?  I am trying to avoid a UNION
0

LVL 58

Accepted Solution

cyberkiwi earned 500 total points
ID: 33729023
Have you tried this?  It guarantees all values of A come before B.

select C
from
(
select part='A', A AS C
from tbl
union all
select part='B', B AS C
from tbl
) X
order by part

But if you really want a mix of A and B, where the sort order is determined by C itself, then

select C
from
(
select part='A', A AS C
from tbl
union all
select part='B', B AS C
from tbl
) X
order by C
0

LVL 58

Expert Comment

ID: 33729031
This question should not be in the SQL Server 2005 zone it seems.
0

Author Comment

ID: 33730565
where do u suggest i post this question?
0

LVL 58

Expert Comment

ID: 33730627
Sorry I don't mean to offend.
The question was posted in both SQL Server 2005 and MySQL.
I can give you answers for both, but from this statement above

"The problem with the CONCAT() function is it combines the values of A and B into one cell."

and CONCAT does not exist in SQL Server, I thought it was squarely in MySql.
I could be wrong.

The queries in http:#a33729023 are however Ansi compliant and apply equally to MySQL and SQL Server.
0

Author Comment

ID: 33730667
no offense taken, sorry for the short response.  I appreciate all your help.
0

## Featured Post

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.