pivot_contactsPivot.sql

/* A pivoted list of contacts */
WITH contactsCTE AS (SELECT a.acctNo
                     , c.name
                     , c.email
                     , ROW_NUMBER() OVER (PARTITION BY a.acctNo ORDER BY c.ContactID) AS ConNumber
                      FROM contacts c JOIN accounts a ON a.acctNo = c.acctNo)
SELECT acctNo
      , MIN(name1) AS Name1 
      , MIN(email1) AS Email1
      , MIN(name2) AS Name2
      , MIN(email2) AS Email2
FROM (SELECT cte.acctNo
             , 'Name' + CAST(cte.ConNumber AS VARCHAR) AS NameNumber --<=== This creates a valid column name
             , cte.name                                              --<=== The actual values that will be attached to the named column
             , 'Email' + CAST(cte.ConNumber AS VARCHAR) AS EmailNumber
             , cte.email
      FROM contactsCTE cte) AS pvt
PIVOT (MIN(name) 
      FOR NameNumber IN ([Name1], [Name2])) AS NamePvt --<== include as many col + # values in this set as want to ultimately return in the row
PIVOT (MIN(email)
       FOR EmailNumber IN ([Email1], [Email2])) AS EmailPvt
GROUP BY acctNo;