March 30, 2012 at 6:42 am
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
March 30, 2012 at 7:01 am
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.
March 30, 2012 at 7:26 am
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)
March 30, 2012 at 4:35 pm
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
April 6, 2012 at 5:21 am
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply