Larry Steinle

A normalized database encourages the storage of information in a key/value format. That’s great for data lookup and storage operations but not always the best for usability. Pivoting data allows one or more rows of data to be organized by columns instead of by row.

To demonstrate various approaches to pivoting data we will create and initialize a simple contact table variable. Each row in the table will list who the contact is, the type of contact information stored and finally the phone and email.

--Create Temporary Table Variable
DECLARE @ContactsTable TABLE (
 ContactID INTEGER,
 Contact VARCHAR(25),
 RecordType CHAR(4),
 Phone VARCHAR(25),
 Email VARCHAR(50)
)


--Load Temporary Table Variable
INSERT INTO @ContactsTable (ContactID, Contact, RecordType, Phone, Email)
VALUES (1, 'Joe', 'Cell', '(352) 786-3321', NULL),
  (2, 'Joe', 'Home', '(352) 786-1516', <a href="mailto:'Joe@Home.com'">'Joe@Home.com'</a>),
  (3, 'Joe', 'Work', '(352) 786-8593', <a href="mailto:'Joe@Work.com'">'Joe@Work.com'</a>),
  (4, 'Karen', 'Cell', '(574) 554-2517', <a href="mailto:'Karen@SmartPhone.com'">'Karen@SmartPhone.com'</a>),
  (5, 'Karen', 'Fax', '(574) 554-5279', NULL),
  (6, 'Karen', 'Work', '(574) 554-1259', <a href="mailto:'Karen@Work.com'">'Karen@Work.com'</a>),
  (7, 'Joe', 'Cell', '(999) 999-9999', NULL) --In a properly constructed table this record should be rejected by the unique index constraint. This record added to demonstrate the effect on the results.

To make the information more user-friendly our code samples will list each contact on a single line with the various contact types in their own column.

A standard select statement displays the rows from the temporary table in a normalized row-by-row layout.

SELECT ContactID,
  Contact,
  RecordType,
  Phone,
  Email
FROM @ContactsTable
ORDER BY Contact,
  RecordType,
  Phone,
  Email

Temporary Table Results

The PIVOT Operator
The PIVOT operator was introduced in SQL Server 2005. The PIVOT operator reduces the amount of code required to pivot the table and is fairly intuitive and easy to read. The act of pivoting the data does require knowing what information in the table will be pivoted ahead of time. In the event that you require dynamic pivoting based upon the results of the table you will need to dynamically generate the pivot statement and use the EXECUTE command.

In the following example a contact will be listed on each row with the work, home, cell and fax phone numbers in separate columns instead of separate rows.

SELECT PivotResults.Contact,
PivotResults.[Work],
PivotResults.[Home],
PivotResults.[Cell],
PivotResults.[Fax]
FROM @ContactsTable AS UnpivotedResults
PIVOT (
MAX(Phone) FOR RecordType IN ([Work], [Home], [Cell], [Fax])
) AS PivotResults

The results returned from the previous query will look as follows:

Single Column Pivot Results

Now that’s not quite what we want. While the data is in the correct columns we want to collapse the rows. There should be only one row for Joe and one for Karen. The reason there are multiple rows per contact is because we didn’t filter our results. The pivot is acting against more data than it should. In the next statement we use an inner query to filter the query to the columns used by the pivot. It is the extra columns that created the extra rows in the previous pivot query results.

SELECT PivotResults.Contact,
  PivotResults.[Work],
  PivotResults.[Home],
  PivotResults.[Cell],
  PivotResults.[Fax]
FROM (
 SELECT Contact,
   RecordType,
   Phone
 FROM @ContactsTable
) AS UnpivotedResults
PIVOT (
 MAX(Phone)
 FOR RecordType
  IN ([Work], [Home], [Cell], [Fax])
) AS PivotResults

Now the results are returned exactly as desired:

Corrected Single Column Pivot Results

A limitation of the PIVOT operator is that only one row can be pivoted into multiple columns. A more traditional, wordy code style must be utilized to pivot multiple rows into multiple columns.

How to Pivot Multiple Columns
The following example demonstrates how to pivot both the phone numbers and the email addresses for each contact into their own respective columns using the GROUP BY operator with the MAX aggregate. Pay close attention to our bad record example that had a duplicate complex key (Contact + RecordType) and the impact on our results. Well designed constraints are a must when storing data that will need to be pivoted!

SELECT Contact,
  MAX(CASE WHEN RecordType = 'Work' THEN Phone ELSE NULL END) AS Work,
  MAX(CASE WHEN RecordType = 'Work' THEN Email ELSE NULL END) AS Work_Email,
  MAX(CASE WHEN RecordType = 'Home' THEN Phone ELSE NULL END) AS Home,
  MAX(CASE WHEN RecordType = 'Home' THEN Email ELSE NULL END) AS Home_Email,
  MAX(CASE WHEN RecordType = 'Cell' THEN Phone ELSE NULL END) AS Cell,
  MAX(CASE WHEN RecordType = 'Cell' THEN Email ELSE NULL END) AS Cell_Email,
  MAX(CASE WHEN RecordType = 'Fax' THEN Phone ELSE NULL END) AS Fax,
  MAX(CASE WHEN RecordType = 'Fax' THEN Email ELSE NULL END) AS Fax_Email
FROM @ContactsTable
GROUP BY Contact

Multiple Column Pivot Results