Solved

Combining 2 columns as one ?

Posted on 2007-11-21
6
277 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
oracle query help 18 99
Query criteria to get previous month's data not working 3 35
PL/SQL - Leading zeros 7 60
SQL Query resolving a string conversion issue 26 39
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…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

867 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now