Solved

Combining 2 columns as one ?

Posted on 2007-11-21
6
284 Views
Last Modified: 2010-04-21
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
Comment
Question by:barney75
  • 3
  • 2
6 Comments
 
LVL 7

Expert Comment

by:SjoerdvW
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

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

Author Comment

by:barney75
ID: 20326463
On both of those - "ORA-01722 - invalid number".  I am using an oracle db by the way...
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 7

Expert Comment

by:SjoerdvW
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

by:
SjoerdvW earned 125 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

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

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question