SQL combine multiple records into one row

Question

I have a table pulling userid's and their personal and work e-mails. I'd like to have one line per id showing both types of e-mails, but I can't figure out how to do that.

    declare @t table(NPI int, email varchar(50), emailtype varchar(50))
    insert into @t
    values(1, 'john@home', 'personal'), (1, 'john@work', 'work');

This is the query I've written so far, which puts this on 2 separate rows:

    select npi, case when emailtype = 'personal' then email end as  personalemail,
    case when emailtype = 'work' then email end as workemail
    from @t;

Current Output:

   npi     personalemail    workemail
    1      john@home          NULL
    1       NULL              john@work

What I'd like to see is:

    npi    personalemail         workemail
     1        john@home           john@work

How can I do this?

Answer 1

This has been asked and answered around here about a million times. It is called conditional aggregation or crosstab. It is faster to write an answer than find one. As an alternative you could use PIVOT but I find the syntax a bit obtuse for me.

select NPI
    , max(case when emailtype = 'personal' then email end) as PersonalEmail
    , max(case when emailtype = 'work' then email end) as WorkEmail
from @t
group by NPI

Answer 2

Use pivot

SELECT
    *
    FROM @T
    PIVOT
    (
        MAX(email)
        FOR EmailType IN
        (
            personal,work
        )
    )Q