Sql server® 2012 t-sql fundamentals

unpivoting Data

Unpivoting is a technique to rotate data from a state of columns to a state of rows. Usually it involves 

querying a pivoted state of the data, producing from each source row multiple result rows, each 

with a different source column value. In other words, each source row of the pivoted table becomes 

potentially many rows, one row for each of the specified source column values. This may be difficult 

to understand at first, but an example should help. 

Run the following code to create and populate a table called EmpCustOrders in the TSQL2012 

sample database.

USE TSQL2012; 

 

IF OBJECT_ID('dbo.EmpCustOrders', 'U') IS NOT NULL DROP TABLE dbo.EmpCustOrders; 

 

CREATE TABLE dbo.EmpCustOrders 

( 

  empid INT NOT NULL 

    CONSTRAINT PK_EmpCustOrders PRIMARY KEY, 

  A VARCHAR(5) NULL, 

  B VARCHAR(5) NULL, 

  C VARCHAR(5) NULL, 

  D VARCHAR(5) NULL 

); 

 

INSERT INTO dbo.EmpCustOrders(empid, A, B, C, D) 

  SELECT empid, A, B, C, D 

  FROM (SELECT empid, custid, qty 

        FROM dbo.Orders) AS D 

    PIVOT(SUM(qty) FOR custid IN(A, B, C, D)) AS P; 

 

SELECT * FROM dbo.EmpCustOrders;

Here’s the output of the query against EmpCustOrders showing its contents.

empid       A           B           C           D 

----------- ----------- ----------- ----------- ----------- 

1           NULL        20          34          NULL 

2           52          27          NULL        NULL 

3           20          NULL        22          30

www.it-ebooks.info

---