SQL Server merge similar JOIN results into one row

Posted on 2007-10-16
Last Modified: 2012-06-21
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?  
Question by:TNGIT
    LVL 18

    Accepted Solution

    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 =
       AND Name1.nametype = 'FirstName'
    left outer join PeoplesNames Name2
       ON =
       AND Name1.nametype = 'SurName'
    LVL 68

    Assisted Solution

    I suggest a function, something like this:

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

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

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now