?
Solved

Combining 2 columns as one ?

Posted on 2007-11-21
6
Medium Priority
?
314 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 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

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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Suggested Courses

718 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