Sql. Return rows as columns

Question

I have a Person table. Each person can have many names in the PersonNames table. I would like to get for each person, 2 of his first names and 2 of his last names in one row. like this:

PersonId | FName1 | Fname2  | Lname1    | Lname2
1          David    Daniekl   Bekernman   Stivens

Person table:

   PersonId
   BirthDate

PersonNames table:

   PersonId
   NameId
   Name
   NameType (e.g; first, last...)

Thanks.

Answer 1

If this is specifically for just two names, this will pick up the names with the MIN and MAX NameId per Person...

SELECT
  Person.PersonId,
  MAX(CASE WHEN Name.nameId = map.minNameId AND Name.nameType = 'first' THEN Name.Name ELSE NULL END) AS fname1,
  MAX(CASE WHEN Name.nameId = map.minNameId AND Name.nameType = 'last'  THEN Name.Name ELSE NULL END) AS lname1,
  MAX(CASE WHEN Name.nameId = map.maxNameId AND Name.nameType = 'first' THEN Name.Name ELSE NULL END) AS fname2,
  MAX(CASE WHEN Name.nameId = map.maxNameId AND Name.nameType = 'last'  THEN Name.Name ELSE NULL END) AS lname2
FROM
  Person
LEFT JOIN
  (SELECT personId, MIN(nameId) AS minNameId, MAX(nameId) as maxNameId FROM PersonNames GROUP BY PersonId) AS map
    ON map.PersonId = Person.PersonId
LEFT JOIN
  PersonNames AS Name
    On Name.PersonId = Person.PersonId
GROUP BY
  Person.PersonId

EDIT

Now that I can see that this is MS SQL Server, there is another option. Similar to others here, but possibly slightly simpler...

WITH
  sequenced_names AS
(
  SELECT
    DENSE_RANK() OVER (PARTITION BY PersonId ORDER BY NameID) AS NameOrdinal,
    *
  FROM
    PersonNames
)
SELECT
  PersonID,
  MAX(CASE WHEN nameOrdinal = 1 AND nameType = 'first' THEN Name END) AS fname1,
  MAX(CASE WHEN nameOrdinal = 1 AND nameType = 'last'  THEN Name END) AS lname1,
  MAX(CASE WHEN nameOrdinal = 2 AND nameType = 'first' THEN Name END) AS fname2,
  MAX(CASE WHEN nameOrdinal = 2 AND nameType = 'last'  THEN Name END) AS lname2
FROM
  sequenced_names
GROUP BY
  PersonID