• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1493
  • Last Modified:

SQL Server merge similar JOIN results into one row

I'm looking to write a query in SQL Server that does the following as efficiently as possible:

I have the following three tables - People, PeoplesNames, Names

The table People holds a unique person.  

The table PeoplesNames holds the primary key of a person with the primary key of a name, along with the type of name it is (first name, surname etc.)

The table Names holds a unique name.

A person would have more than one name - for example, a first name, middle name and a surname.

I am currently using a JOIN statement to get a person and their names.  

This JOIN statement, however, will return more than one row for each person - the person's Id will stay the same in three or four rows (however many names they have) but the name will change.  

I am currently doing the processing and merging of the names in my program, not the SQL.  

Is there any way with SQL to return, for example, first name, middle name, surname all in one row in different fields?  
0
TNGIT
Asked:
TNGIT
2 Solutions
 
UnifiedISCommented:
You need to join PeoplesNames once for each type of name you are returning
something like this:
SELECT Name1.Name AS FirstName, Name2.Name as SurName
FROM People
left outer join PeoplesNames Name1
   ON Name1.fk = People.pk
   AND Name1.nametype = 'FirstName'
left outer join PeoplesNames Name2
   ON Name2.fk = People.pk
   AND Name1.nametype = 'SurName'
0
 
Scott PletcherSenior DBACommented:
I suggest a function, something like this:


CREATE FUNCTION GetPersonName (
    @person_id INT
)
RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @name VARCHAR(200)
SELECT @name = ISNULL(@name + ' ', '') + n.name
FROM PeoplesNames pn
INNER JOIN Names n ON pn.name_id = n.id
WHERE pn.person_id = @person_id
ORDER BY pn.nameType
RETURN @name
END --FUNCTION


SELECT dbo.GetPersonName(id) AS FullName
FROM People
ORDER BY 1
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now