Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Combining 2 columns as one ?

Posted on 2007-11-21
Medium Priority
317 Views
Hello

I am hoping that there is some way in SQL to combine the values of 2 columns into one in the ResultSet. Can you tell me if this is can be done?

Teachers

teacher       class
-------------------------
Mr Doyle     Math

Supervisors

supervisor     class
---------------------------
Mr Reid          Math

I want to display the following:

class              teachers
--------------------------------
Math              Mr Doyle, Mr Reid

Any suggestions?
0
Question by:barney75
• 3
• 2

LVL 7

Expert Comment

ID: 20326392
SELECT dbo.Supervisors.class, RTRIM(dbo.Teachers.teacher) + ', ' + dbo.Supervisors.supervisor AS Teacher FROM dbo.Supervisors INNER JOIN dbo.Teachers ON dbo.Supervisors.class = dbo.Teachers.class
0

LVL 39

Expert Comment

ID: 20326393
select T.class , T.teacher + ',' + s.supervisor as teachers
from supervisor S , Teachers T
0

Author Comment

ID: 20326463
On both of those - "ORA-01722 - invalid number".  I am using an oracle db by the way...
0

LVL 7

Expert Comment

ID: 20326517
Don't know oracle, but since the error say's invalid number it probably aspected numbers in the columns. Try to replace the + with &
0

LVL 7

Accepted Solution

SjoerdvW earned 500 total points
ID: 20326528
You can comabine two columns using concatenating operator || (double vertical bars).
So it shou;d be something like this:

SELECT dbo.Supervisors.class, RTRIM(dbo.Teachers.teacher) || ', ' || dbo.Supervisors.supervisor AS Teacher FROM dbo.Supervisors INNER JOIN dbo.Teachers ON dbo.Supervisors.class = dbo.Teachers.class
0

Author Closing Comment

ID: 31410319
Thank you SjoerdvW, the || did the trick!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Integration Management Part 2
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
###### Suggested Courses
Course of the Month15 days, 21 hours left to enroll