Geek Answers Handbook

SQL Help: Selecting a Combine One to Large Relationship Statement

For example, I have two tables. The first table is the student, and the second table is the courses that the student takes. How can I use the select statement so that I can see two columns of students and courses so that courses are separated by commas.

Thanks.

+1
source share
8 answers

Assuming you are using SQL Server 2005:

This should do what you need - obviously, replace the fields as needed:

For demonstration purposes, consider the following two table structures:

Students( STU_PKEY Int Identity(1,1) Constraint PK_Students_StuPKey Primary Key, STU_NAME nvarchar(64) ) Courses( CRS_PKEY Int Identity(1, 1) Constraint PK_Courses_CrsPKey Primary Key, STU_KEY Int Constraint FK_Students_StuPKey Foreign Key References Students(STU_PKEY), CRS_NAME nvarchar(64) )

Now this request should complete the task you are doing:

 Select s.STU_PKEY, s.STU_NAME As Student, Stuff(( Select ',' + c.CRS_NAME From Courses c Where s.STU_PKEY = c.STU_KEY For XML Path('') ), 1, 1, '') As Courses From Students s Group By s.STU_PKEY, s.STU_NAME

Easier than the currently accepted answer ...

+9
source 
 create table Project (ProjectId int, Description varchar(50)); insert into Project values (1, 'Chase tail, change directions'); insert into Project values (2, 'ping-pong ball in clothes dryer'); create table ProjectResource (ProjectId int, ResourceId int, Name varchar(15)); insert into ProjectResource values (1, 1, 'Adam'); insert into ProjectResource values (1, 2, 'Kerry'); insert into ProjectResource values (1, 3, 'Tom'); insert into ProjectResource values (2, 4, 'David'); insert into ProjectResource values (2, 5, 'Jeff'); SELECT *, (SELECT Name + ' ' AS [text()] FROM ProjectResource pr WHERE pr.ProjectId = p.ProjectId FOR XML PATH ('')) AS ResourceList FROM Project p -- ProjectId Description ResourceList -- 1 Chase tail, change directions Adam Kerry Tom -- 2 ping-pong ball in clothes dryer David Jeff
+1
source

It depends on which server you are using. SQL Server? MySQL? Other?

0
source

As a rule, we are talking about the connection:

 SELECT S.*, SC.* FROM Students S INNER JOIN Student_Courses SC ON S.student_id = SC.student_id

However, this will give you one line per course. SQL does not allow you to easily get a set of courses in the form of a comma-separated list on the same line (this is not a set-based operation). There are different ways to do this, depending on the vendor, including the loop.

0
source

I think this MySQL page will help you with this. http://dev.mysql.com/doc/refman/4.1/en/group-by-modifiers.html

0
source

So you want to see:

 'Jade', 'Math, English, History' 'Kieveli', 'History, Biology, Physics'

Yes, comma separation is always advisable, but SQL is not very good at that. Here's the approach I always planned to use:

Create a function on the SQL server that uses the cursor to loop over the subquery (sorry - I did not fully test it):

 CREATE FUNCTION commacourselist(@studentname varchar(100)) RETURNS @List varchar(4096) AS BEGIN DECLARE @coursename varchar(100) DECLARE FOR SELECT course.name FROM course WHERE course.studentname = @studentname OPEN coursecursor FETCH NEXT FROM coursecursor INTO @coursename WHILE @@FETCH_STATUS = 0 BEGIN IF @List = '' BEGIN SET @List = @coursename END ELSE BEGIN SET @List = @List + ',' + @coursename END FETCH NEXT FROM coursecursor INTO @coursename END CLOSE coursecursor DEALLOCATE coursecursor RETURN END GO

Then call the function in the request:

 SELECT student.name, commacourselist( student.name ) FROM student
0
source

You can use UDF, which creates cursors through related records and concatenates the returned string together, but it will be expensive - if you let it go, make sure your cursor is READ_ONLY FAST_FORWARD

0
source

Are you using 2005 or 2008? If so, find the PIVOT command. It should be more efficient than a cursor function.

0
source

More articles:


All Articles