Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » SQL Anywhere Server - SQL Reference » SQL statements » Alphabetical list of SQL statements

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.

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 [,...] ) 

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 ] [,...] )


    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.


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.


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

Side effects


  • ANSI/ISO SQL Standard

    Not in the standard.


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:

  LastName VARCHAR(64),
  FirstName VARCHAR(64),
  Home VARCHAR(32),
  Mobile VARCHAR(32),
  AltPhone VARCHAR(32)
INSERT 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-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
  SELECT ContactID, Home, Mobile, AltPhone 
  FROM MyCustomers
) AS MyUnpivotSource
  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 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
 ( ( 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