0

This is my select statement:

SELECT lastName + ',' + firstName as Name,  
       classNumber
FROM STUDENT 
RIGHT JOIN ENROLL ON Enroll.stuid = Student.stuid

and the output:

name           classnumber
--------------------------
Smith,Tom      ART103A  
Smith,Tom      HST205A  
Chin,Ann       ART103A  
Chin,Ann       CSC201A  
Chin,Ann       MTH103A  
McCarthy,Owen  ART103A   
McCarthy,Owen  MTH103C  
Rivera,Jane    CSC201A  
Rivera,Jane    MTH101B

I would like it so that each name only appears in the first column once. Any help or idea greatly appreciated.

Wolph
  • 78,177
  • 11
  • 137
  • 148
Mike
  • 1
  • 1
  • 1
    What version of sql (MS, My, etc.)? And there aren't any `[code]` tags, but there's a button in the question form that you can use after you're text's selected. ;-) -- TY OMG Ponies & Michael for edit. – Brad Christie Dec 13 '10 at 02:18
  • I am using sql server 2008 sorry about that forgot to put that part in there. – Mike Dec 13 '10 at 02:21
  • You can't make group on the sql side, but you can do this operation on your interface like datagrid by using Group column – Waleed A.K. Dec 13 '10 at 03:16

2 Answers2

0

As the rows are all independent from each other, no row can know if it's the first one.

What you could do is to GROUP BY Name and put all the classNumbers together in a list like this:

SELECT lastName + ',' + firstName AS Name,
       GROUP_CONCAT(classNumber SEPARATOR '\n') AS classNumbers
FROM student
RIGHT JOIN Enroll ON (Student.stuid = Enroll.stuid)
GROUP BY Name
AndreKR
  • 32,613
  • 18
  • 106
  • 168
  • `GROUP_CONCAT` is MySQL only -- the OP clarified, they're using SQL Server. – OMG Ponies Dec 13 '10 at 02:24
  • Then I'll keep my answer and in addition provide these two links. ;) http://explainextended.com/2010/06/21/group_concat-in-sql-server/ and http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005 – AndreKR Dec 13 '10 at 02:26
0

if only one class will appear.

SELECT lastName + ',' + firstName as Name, classNumber
FROM STUDENT 
RIGHT JOIN 
(select Student.stuid as stuid, min(classNumber) as ClassNumber 
 from ENROLL Enroll inner Join Student
 ON (Student.stuid = Enroll.stuid) group by Student.stuid) AS NewEnrol 
 ON NewEnroll.stuid = Student.stuid

Or

SELECT lastName + ',' + firstName as Name,Min(classNumber)
FROM STUDENT 
RIGHT JOIN ENROLL ON Enroll.stuid = Student.stuid
Group By lastname+','+firstname
Waleed A.K.
  • 1,596
  • 13
  • 13
  • I thank you all for the quick replies. I am looking for the output to look exactly like it does in the op but the duplicate names are gone so as to get rid of the confusion that it is two people with the same name it is one person taking multiple classes – Mike Dec 13 '10 at 02:51