Trying to get multiple related values on one row

Question

I've got the following sql select statement which returns the information I want. It seems to work reliably although I have lots of left joins, tryomg to get the data out of for a pupil and any other related data. The only issue I seem to of stumbled on is returning the nationality data. My query is fine if a pupil has one nationality, however if they have two, I'd like to add/append the second value at the end of the first on the same row. Anyone advise me the best way to achieve this? Is this situation where I would use a subquery

SELECT p.pk_PupilID,p.PupilNumber,p.PupilStatus,pn.Surname ,pn. Forename,p.Form,d.DepartmentCode,p.BoardingStatus,e.StartTerm,e.StartAcademicYear,ny.Form,ny.BoardingStatus, c.Nationality 
FROM Pupil p
LEFT JOIN Person pn ON p.pk_PupilID = pn.pk_PersonID 
LEFT JOIN NextAcademicYear ny ON p.pk_PupilID = ny.fk_PupilID 
LEFT JOIN Department d ON p.fk_DepartmentID=d.pk_DepartmentID 
LEFT JOIN Enrolment e ON p.pk_PupilID=e.fk_PupilID 
LEFT JOIN Nationality n ON p.pk_PupilID = n.fk_PersonID 
LEFT JOIN Country c ON n.fk_ISO2Code = c.ISO2Code 
WHERE p.pk_PupilID IN( '" & $IDs & "' )

score 1 · Answer 1 · answered Aug 25 '16 at 22:23

The results you are getting are inline with how sql works.

What I generally do for what you are tying to do is make some other code like a function that combines the multiple nationalities in to a single field. For example if they returned two rows with your query "USA" in one "Japan" in the other. In my example they would be in a single row of data with "USA,Japan" in one column for the nationality. Perhaps "USA,Japan,UK" if they had three nationalities but there are limits to this process only helps you if there are a few like 4 or less.

Be careful you don't get into a Cartesian join situation with this type of query. If they can have two departments also or two of any of the other things you link to you are in a Cartesian Join situation and this solution will not work.

score 1 · Answer 2 · edited May 23 '17 at 12:00

1

This is normal, expected behavior. The old school answer to this is, "handle it in your application."

For most modern RDBMSs, you can use string aggregation functions.

MySQL has GROUP_CONCAT().

Oracle has LISTAGG().

PostgreSQL has STRING_AGG().

SQL Server has an absurdly arcane STUFF() FOR XML PATH ('') syntax that makes your eyes bleed, but it does work.

edited May 23 '17 at 12:00

Community

1
1

answered Aug 25 '16 at 22:32

Bacon Bits

30,782
5
59
66

Unnikrishnan R · Answer 3 · 2016-08-26T07:55:22.673

for SQL server ,use the below script for achieving the desired result.

SELECT p.pk_PupilID,p.PupilNumber,p.PupilStatus,pn.Surname ,pn. Forename,p.Form,d.DepartmentCode,p.BoardingStatus,e.StartTerm,e.StartAcademicYear,ny.Form,ny.BoardingStatus
  , stuff((SELECT distinct ', ' + ISNULL(c.Nationality,'')
       FROM  Nationality n 
            JOIN Country c ON n.fk_ISO2Code = c.ISO2Code  
       where  p.pk_PupilID = n.fk_PersonID AND c.Nationality IS NOT null
       FOR XML PATH('')),1,1,'') Nationality 
FROM Pupil p
LEFT JOIN Person pn ON p.pk_PupilID = pn.pk_PersonID 
LEFT JOIN NextAcademicYear ny ON p.pk_PupilID = ny.fk_PupilID 
LEFT JOIN Department d ON p.fk_DepartmentID=d.pk_DepartmentID 
LEFT JOIN Enrolment e ON p.pk_PupilID=e.fk_PupilID 
WHERE p.pk_PupilID IN( '" & $IDs & "' )

score 0 · Answer 4 · answered Aug 29 '16 at 19:35

Something to think about. What you seek does not exactly follow into main stream SQL that is supported on all Sql platforms or by all Sql versions. The "Stuff" command or variations to it are available on a lot of the newer sql versions. I think MsSql support stated supporting it with version 2008. A function will work in all Sql platforms but it requires more work and is more Platform and Version specific. If you were for example working on both Sql 2005 and Sql 2008 and you used the stuff command. I believe it will not work on Sql 2005. Just trying to help you make an informed decision. That being said the syntax of your function is Platform specific. You could write a function that would do this and work all the way back to MsSql 2000 if that was what you were working on. But it would not work on Pgsql,MySql, Oracle, Db2, MariaDb, etc. It may work on Sybase though.

Trying to get multiple related values on one row

4 Answers4