SQL Help: Select statement Concatenate a One to Many relationship

Question

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

Thanks.

BenAlabaster · Accepted Answer · 2008-12-15T15:16:35.033

Assuming you're using SQL Server 2005:

This should do what you're after - obviously replace fields as you need:

For demo 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 query should do the job you're after:

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

Way simpler than the currently accepted answer...

Thanks I used this one and your right it is simple. Thanks :) — , Dec 16 '08 at 15:01

score 2 · Answer 2 · edited Dec 28 '11 at 16:00

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

score 0 · Answer 3 · answered Dec 15 '08 at 14:14

0

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

answered Dec 15 '08 at 14:14

Joel Coehoorn

399,467
113
570
794

score 0 · Answer 4 · answered Dec 15 '08 at 14:16

Generally, what you're talking about is a join:

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

However, that will give you one row per course. SQL doesn't make it easy to get the set of courses as a comma delimited list in a single row (that's not a set-based operation). Depending on the vendor, there are different ways to do it, involving looping.

score 0 · Answer 5 · answered Dec 15 '08 at 14:17

0

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

answered Dec 15 '08 at 14:17

hash

975
2
14
17

score 0 · Answer 6 · edited Sep 11 '17 at 20:01

0

So you want to see:

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

Yes, comma separated is always desirable, but SQL isn't good at it. Here's the approach I was always planning on using:

Create a Function on the SQL server that uses a cursor to loop over the subquery (sorry - I didn't fully test this):

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

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

edited Sep 11 '17 at 20:01

C B

1,677
6
18
20

answered Dec 15 '08 at 14:17

Kieveli

10,944
6
56
81

1

A cursor? No! 2005's PIVOT could probably be used for this, or a custom aggregate, or the Stuff() option below, or just about _anything_ but a cursor. – Joel Coehoorn Dec 15 '08 at 16:03
1

Joel - do you have an example of how to use PIVOT for this? I tried to figure that method out, but I couldn't wrap my head around it. – BenAlabaster Dec 15 '08 at 16:52
I saw a vague reference on how to COALESCE() to a string list, but couldn't find any concrete implementation. I've done some PIVOTs before, and never did they show promise of creating a comma separated list. – Kieveli Dec 15 '08 at 18:25
The PIVOT will give you one column for the studentname and one column for each course (variable non-null columns for each row). You were asking for two columns, so a pivot won't exactly work. – Kieveli Dec 15 '08 at 20:07
@Kieveli: Coalesce() is great if you've got multiple columns and you want to concatenate them to a single column, but not so great if you want to concatenate multiple rows to a single. – BenAlabaster Dec 16 '08 at 03:45

score 0 · Answer 7 · answered Dec 15 '08 at 14:18

0

You can use a UDF that cursors through the related records and concats a return string together but this will be expensive - if you give it a go make sure your cursor is READ_ONLY FAST_FORWARD

answered Dec 15 '08 at 14:18

Rich Andrews

4,168
3
35
48

score 0 · Answer 8 · answered Dec 15 '08 at 14:22

0

Are you using 2005 or 2008? If so, then lookup the PIVOT comand. It should be more efficient than a function with a cursor.

answered Dec 15 '08 at 14:22

HLGEM

94,695
15
113
186

SQL Help: Select statement Concatenate a One to Many relationship

8 Answers8

Linked