Joining tables with two tables but displayed in only one row
-
Hi All,
Here is my problem:
Suppose there are two tables A and B.
A has Student Details.
B has subjects related to each student.
So if a student 'abc' (from table A) has 2 subjects 'sub1' and 'sub2' ( from table B), the select join query displays the data like this:
Student | Subject
--------------------
abc | sub1
abc | sub2
But I want them to be displayed like:
Student : abc ; Subject : sub1,sub2
So in reality the number of subjects for a student is not actually defined. So another student may have more than 2 subjects.
Is there anyway I can do this?
Thanks in advance.
Prady
-
Yes you can. You will need to use dynamic SQL (search for "dynamic cross-tab".
However, if the number of columns does vary, you better to do so in a client/server tier of application as it will be much more sufficient and appropriate.
-
Even if I try dynamic cross tab, I would still be stuck since I wouldnt be able to do a 'Group By' on it.
And the result : Student : abc ; Subject : sub1,sub2
is expected to be in one row (one cell)
-
Use FOR XML PATH('') to concatenate your string together. There are several articles on how to do this. In my quick scan, this covered the basics well. Creating a comma-separated list (SQL Spackle)[/url]
Since you already have a table with the distinct student ID, you don't need to use a CTE to create one.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
drew.allen (3/30/2012)
Use FOR XML PATH('') to concatenate your string together. There are several articles on how to do this. In my quick scan, this covered the basics well. Creating a comma-separated list (SQL Spackle)[/url]Since you already have a table with the distinct student ID, you don't need to use a CTE to create one.
Drew
Thanks Drew. I was able to get the desired result with that information
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply