Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
/* 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.