Query returning more than one row with the same name

I am having trouble with an SQL query returning more than one row with the same name, using this query:

SELECT * FROM People P JOIN SpecialityCombo C ON P.开发者_如何转开发PERSONID = C.PERSONID JOIN Speciality S ON C.GROUPID = S.ID;

People contains information on each person, Specialty contains the names and ID of each specialty and SpecialityCombo contains information about the associations between People and their Speciality, namely each row has a PERSONID and a Speciality ID (trying to keep it normalised to some extent).

My query works in that it returns each Person and the name of their specialty, but it returns n rows for the number of specialitys they want, because each specialty returns the same row 'name'. What I want is it to return just one row containing each speciality. How can I do this?

Use left join to overcome return no rows when specialty not found

SELECT P.*, 
GROUP_CONCAT(S.NAME) AS specialties 
FROM People P 
LEFT JOIN JOIN SpecialityCombo C ON P.PERSONID = C.PERSONID 
LEFT JOIN JOIN Speciality S ON C.GROUPID = S.ID 
GROUP BY P;

You cannot turn rows into columns, SQL doesn't support pivoting. Your best option is to take the resultset with each specialty in a row and use Excel or some programming to pivot. An alternative is to just concatenate all the specialties inside a single column, depending on your sql server. In mysql you could do that as below:

SELECT P.*, GROUP_CONCAT(S.NAME SEPARATOR '|') AS specialties FROM People P JOIN SpecialityCombo C ON P.PERSONID = C.PERSONID JOIN Speciality S ON C.GROUPID = S.ID GROUP BY P;

This article is a wonderfully exhaustive treatment of concatenating row values, which is what you need to do here (join all of the specialty results into a single row and column, with a result something like "putting, chipping, driving").

As you'll see, there are many ways to accomplish this, depending on what you know and expect from the data (numbers of specialties per person, for example).

The article rightly points out that while doable, this is not an appropriate task for T-SQL, and it is more favourable to return the full result set and manage the merging and/or formatting in a client-side application.