UNPIVOT clause

Unpivots compatible-type columns of a table expression in a FROM clause (FROM unpivoted-derived-table expression) into rows in a derived table. Unpivoting is used to normalize data, for example when you have similar data stored in multiple columns in tables and you want to return it in one column.

Syntax

FROM unpivoted-derived-table

unpivoted-derived-table : 
unpivot-source-table UNPIVOT [ { INCLUDE | EXCLUDE } NULLS ]  ( unpivot-clause ) [ AS ] correlation-name

unpivot-clause : 
unpivot-value-clause unpivot-for-clause unpivot-in-clause

unpivot-value-clause : 
value-column
| ( value-column [,...] ) 

unpivot-for-clause : 
FOR unpivot-column 

unpivot-in-clause : 
IN ( unpivot-old-column [[ AS ] unpivot-old-column-alias ] [,...] )
| IN ( ( unpivot-old-column [,...] ) [[ AS ] unpivot-old-column-alias ] [,...] )

Parameters

{ INCLUDE | EXCLUDE } NULLS
Specify whether to include or exclude NULL values in the results for value-column.The default behavior is EXCLUDE NULLS.
unpivot-value-clause
Specify name(s) for the new columns in the unpivoted derived table that will hold unpivoted values.
unpivot-for-clause
Specify a column to unpivot the data for.
unpivot-in-clause
Specify the values of unpivot-for-clause to unpivot data for.

Remarks

An unpivoted derived table contains a subset of the columns in unpivot-source-table, plus additional columns specified in the unpivot-value-clause and unpivot-column. The columns in the IN clause must be found in unpivot-source-table but are not present in the unpivoted derived table.

If the IN clause has I elements, then each row in unpivot-source-table is transformed into I rows in the unpivoted derived table. That is, each value tuple corresponding to an item in the IN clause generates a new row in the unpivoted derived table. The unpivot-column value for each row is set to the value of the IN clause alias for that item, while the new columns specified in unpivot-value-clause are set to the value tuple of the original row for that item.

Each item in the IN clause requires compatible domains with any other items in the IN clause. The data type for the new columns specified in unpivot-value-clause corresponds to a super type of these compatible domains. The data type of the values in unpivot-column is NCHAR, and its values are the aliases defined in the IN clause.

Privileges

You must have SELECT privileges on the objects referenced in unpivot-source-table.

Side effects

None.

Standards

ANSI/ISO SQL Standard
Not in the standard.

Example

Suppose you have a table called MyCustomers, containing the names of your contacts and the various phone numbers they have. The following statements create such a table, populate it with fictitious data, and then query the table:

CREATE TABLE MyCustomers
( ContactID INT PRIMARY KEY, 
  LastName VARCHAR(64),
  FirstName VARCHAR(64),
  Home VARCHAR(32),
  Mobile VARCHAR(32),
  AltPhone VARCHAR(32)
);
INSERT MyCustomers
  ( ContactID, LastName, FirstName, Home, Mobile, AltPhone )
VALUES
  ( 1, 'Bringle', 'Susan', '111-593-1451', '222-693-7620', NULL ),
  ( 2, 'Hoffsteter', 'Garth', '113-249-6622', NULL, NULL),
  ( 3, 'Zenibar', 'Austin', NULL, '171-765-8730', '888-536-5324' );
SELECT * FROM MyCustomers;

ContactID	LastName	FirstName	Home	Mobile	AltPhone
1	Bringle	Susan	111-593-1451	222-693-7620	(NULL)
2	Hoffsteter	Garth	113-249-6622	(NULL)	(NULL)
3	Zenibar	Austin	(NULL)	171-765-8730	888-555-5555

For each contact, the contact numbers are stored in three separate columns: Home, Mobile, and AltPhone.

Now, suppose that you need to gather some statistics related to phone numbers that your customer service representatives call. You want to see a list of all phone numbers that are called, with the numbers sorted so that you can identify trends such as similar exchange numbers. To get the results you need, you must unpivot the Home, Mobile, and AltPhone columns into a single column that contains one row per phone number, sorted by phone number. Your SELECT statement might look as follows:

SELECT ContactID, PhoneNumber, PhoneType
FROM
(
  SELECT ContactID, Home, Mobile, AltPhone 
  FROM MyCustomers
) AS MyUnpivotSource
UNPIVOT EXCLUDE NULLS 
(
  PhoneNumber FOR PhoneType IN ( Home, Mobile, AltPhone )
) 
AS MyUnpivotedTable 
ORDER BY PhoneNumber;

ContactID	PhoneNumber	PhoneType
1	111-593-1451	Home
2	113-249-6622	Home
3	171-765-873	Mobile
1	222-693-7620	Mobile
3	888-536-5324	AltPhone

The results show how the UNPIVOT operation normalizes the three columns of phone number data found in the MyCustomers table, placing them into a single phone number column called PhoneNumber, which is unpivot-value-clause in this example. The values in the PhoneType column, which is unpivot-column in this example, are determined by the column in which the phone number value was found in unpivot-source-table.

The following query extracts the phone numbers for contacts and their customers in the Customers table into an unpivoted derived table where the phones are listed, and the provenance - customer phone or contact phone - is recorded in the unpivot column People.

SELECT DISTINCT * 
FROM 
 ( SELECT CO.CustomerID, 
          C.City customer_city, 
          C.State customer_state, 
          c.Phone customer_phone,
          CO.City contact_city, 
          CO.State contact_state, 
          CO.Phone contact_phone
   FROM Customers C 
   JOIN Contacts CO 
   WHERE  C.State IN ( 'NJ', 'NY' ) ) UnpivotSourceTable
UNPIVOT
 ( ( City, State, Phone ) FOR People IN 
     ( 
       ( customer_city, customer_state, customer_phone ) AS CustomerPhone, 
       ( contact_city, contact_state, contact_phone ) AS ContactPhone 
     )
 ) UnpivotedDerivedTable
ORDER BY CustomerID, People;

For example, the unpivot source table UnpivotSourceTable contains the following row (among others):

CustomerID	customer_city	customer_state	customer_phone	contact_city	contact_state	contact_phone
101	Kingston	NJ	2015558966	Hespeler	NJ	6035550988

The query unpivots the data into new rows identified by CustomerID 101 in the result set table below.

CustomerID	People	City	State	Phone
101	ContactPhone	Hespeler	NJ	6035550988
101	CustomerPhone	Kingston	NJ	2015558966
111	ContactPhone	Cornwall	NY	6175557956
111	ContactPhone	Cornwall	NY	6175558890
111	CustomerPhone	Hastings	NY	3155554486
116	ContactPhone	Cornwall	NY	6175552222
116	ContactPhone	Cornwall	NY	6175559877
116	CustomerPhone	Stayner	NY	9145553817
147	ContactPhone	Hespeler	NJ	6035551200
147	CustomerPhone	Campbellford	NJ	9085556021