Solved

Combining 2 columns as one ?

Posted on 2007-11-21
6
291 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!

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.

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

829 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