I don’t find myself pivoting data often but when I do I seem to always need to do the “complicated” pivot. You know, the one where you have a collection of unique values like email addresses where the value isn’t the column name but you want the 4 email addresses connected to one person in a single row rather than four rows. I can never remember how to do that so here is my personal internet bookmark for that code. Thanks to the late Robert Davis who gave the answer I needed back in 2015. Robert was a wonderful and generous contributor to the SQL Family his site is full of useful information and is still available here.
The solution is to add the categories you want as eventual columns to your result set before pivoting (it sounds simple enough but again then I can never remember it). So if you have say an account with multiple contacts:
| /* A list of contacts */ | |
| SELECT a.acctNo, c.name, c.email | |
| FROM contacts c JOIN accounts a ON a.acctNo = c.acctNo | |
| ORDER BY a.acctNo, c.contactNo |
This returns a list of results such as:
| acctNo | name | |
| 1001 | Susan | susan@thesuze.com.edu |
| 1001 | Mark | mark@thesuze.com.edu |
| 1002 | Aoife | theboss@bagpipebattle.com.edu |
The one simple trick is to create a value for each row that will be repeated it can be used as a column name when you pivot:
| /* 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; |
Simply repeat the structure of creating a valid column name (‘String’ + CAST(counter as VARCHAR)) and and you can include a pivot across as many different columns as need to be included. Our short list above now becomes:
| acctNo | Name1 | Email1 | Name2 | Email2 |
| 1001 | Susan | susan@thesuze.com.edu | Mark | mark@thesuze.com.edu |
| 1002 | Aoife | theboss@bagpipebattle.com.edu | NULL | NULL |
Hopefully now in the future I’ll remember I wrote this and I won’t have to go searching for the solution (again).
One comment