Split 1 email column to 2 column in SQL Server

Question

I have these records:

Name         mail
------------------
George        a
George        m
Paul          b
Lisa          c
Lisa          n
Sara          d

I want a consolidated list like this:

Name        mail1      mail2
------------------------------
George          a          m
Paul            b          -
Lisa            c          n
Sara            d          -

How could I do that?

Answer 1

You can use row_number to generate numbers to the rows and then pivot using it, something like this:

select * from
(
  select *, 
    row_number() over (partition by Name order by mail) as RN
  from yourtable
) S
pivot (
  max(mail) for RN in ([1],[2])
) p