Flattening out a normalized SQL Server 2008 R2 database

Question

I am working with SQL Server 2008 R2.

I have 3 tables the data is normalized and I am looking to grab the 'Home' and 'Cell' phone for Bob Dole. However I need to only get the highest sequence phone number of each type. (below is an example of Bob Dole having 2 cell phones and the sequence number for each is 2 and 3 respectively)

Table PersonPhoneNumber

PersonPhoneNumberId  Person      PhoneNumberId    PhoneNumberTypeId    Sequence
Guid - vvv           Bob Dole    Guid - A         1                    1
Guid - www           Bob Dole    Guid - B         2                    2
Guid - xxx           Bob Dole    Guid - C         2                    3

Table PhoneNumber

PhoneNumberId   Number
Guid - A        111-111-1111
Guid - B        222-222-2222
Guid - C        333-333-3333

Table PhoneNumberType

PhoneNumberTypeId     PhoneNumberType
1                     Home
2                     Cell

My desired output would be this (notice that I only returned the first Cell number.):

Person      Home            Cell
Bob Dole    111-111-1111    222-222-2222

I have been having issues flattening out the data

Any help with the query would be great!

Answer 1

Here's an example with a sub-query to get the first sequence number for each Type. The outer query then uses a CASE statement to create the Home and Cell Columns.

SELECT  P.Person
        ,MAX(CASE WHEN P.PhoneNumberTypeId = 1 THEN N.Number ELSE NULL END) AS Home
        ,MAX(CASE WHEN P.PhoneNumberTypeId = 2 THEN N.Number ELSE NULL END) AS Cell
FROM    PersonPhoneNumber P
INNER JOIN
        PhoneNumber N
ON      P.PhoneNumberId = N.PhoneNumberId 
INNER JOIN
        (
        SELECT  Person
                ,PhoneNumberTypeId
                ,MIN(Sequence) AS FIRST_NUM
        FROM    PersonPhoneNumber
        GROUP BY
                Person
                ,PhoneNumberTypeId        
        ) SQ1
ON      P.Person = SQ1.Person
AND     P.PhoneNumberTypeId = SQ1.PhoneNumberTypeId
AND     P.Sequence = SQ1.FIRST_NUM
GROUP BY
        P.PERSON

Answer 2

You can use a row_number() and an aggregate function with CASE expression to convert the data from rows to columns:

select person,
  max(case when rn = 1 and PhoneNumberType = 'Home' then number end) home,
  max(case when rn = 1 and PhoneNumberType = 'Cell' then number end) cell
from
(
  select ppn.person, pn.number,
    pt.PhoneNumberType,
    row_number() over(partition by ppn.person, ppn.PhoneNumberTypeId
                      order by ppn.sequence) rn
  from PersonPhoneNumber ppn
  inner join PhoneNumber pn
    on ppn.PhoneNumberId = pn.PhoneNumberId
  inner join PhoneNumberType pt
    on ppn.PhoneNumberTypeId = pt.PhoneNumberTypeId
) d
group by person;

See SQL Fiddle with Demo

This could also be done using the PIVOT function:

select person,
  home, 
  cell
from
(
  select ppn.person, pn.number,
    pt.PhoneNumberType,
    row_number() over(partition by ppn.person, ppn.PhoneNumberTypeId
                      order by ppn.sequence) rn
  from PersonPhoneNumber ppn
  inner join PhoneNumber pn
    on ppn.PhoneNumberId = pn.PhoneNumberId
  inner join PhoneNumberType pt
    on ppn.PhoneNumberTypeId = pt.PhoneNumberTypeId
) d
pivot
(
  max(number)
  for PhoneNumberType in (Home, Cell)
) piv
where rn = 1;

See SQL Fiddle with Demo