SQL SERVER – Example of PIVOT UNPIVOT Cross Tab Query in Different SQL Server Versions

Transforming rows to columns (PIVOT/CROSS TAB) and columns to rows (UNPIVOT) may be one of the common requirements that all of us must have seen several times in our programming life. SQL Server 2005 introduced two new operators: PIVOT and UNPIVOT that made writing cross-tab queries easier.

My friend and SQL Server MVP Jacob Sebastian has posted an example that transform rows to columns using PIVOT operator. The reverse operation of PIVOT is UNPIVOT. PIVOT operator is available only in SQL Server 2005/2008. It does not exists in SQL Server 2000. Developers who are still using SQL Server 2000 should upgrade to SQL Server 2005 or SQL Server 2008 (recommended).

While discussion this issue with Jacob another day he wrote following posts so fast it was very impressive.

TSQL Lab 15 – Another PIVOT Query Example
TSQL Lab 16 – Generating CROSS TAB results in SQL Server 2000
TSQL Lab 17 – An example using UNPIVOT (Transforming Columns to Rows)

Articles written by me previously.

SQL SERVER – PIVOT and UNPIVOT Table Examples
SQL SERVER – PIVOT Table Example
SQL SERVER – UNPIVOT Table Example

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

SQL SERVER – PIVOT and UNPIVOT Table Examples

I previously wrote two articles about PIVOT and UNPIVOT tables. I really enjoyed writing about them as it was interesting concept. One of the Jr. DBA at my organization asked me following question.

“If we PIVOT any table and UNPIVOT that table do we get our original table?”

I really think this is good question. Answers is Yes, you can but not always. When we pivot the table we use aggregated functions. If due to use of this function if data is aggregated, it will be not possible to get original data back.

Let me explain this issue demonstrating simple example.
USE AdventureWorks
GO
-- Creating Test Table
CREATE TABLE Product(Cust VARCHAR(25), Product VARCHAR(20), QTY INT)
GO
-- Inserting Data into Table
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','VEG',2)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','SODA',6)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','MILK',1)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','BEER',12)
INSERT INTO Product(Cust, Product, QTY)
VALUES('FRED','MILK',3)
INSERT INTO Product(Cust, Product, QTY)
VALUES('FRED','BEER',24)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','VEG',3)
GO
-- Selecting and checking entires in table
SELECT *
FROM Product
GO
-- Pivot Table ordered by PRODUCT
SELECT PRODUCT, FRED, KATE
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT
(SUM(QTY) FOR CUST IN (FRED, KATE)) AS pvt
ORDER BY PRODUCT
GO
-- Pivot Table ordered by CUST
SELECT CUST, VEG, SODA, MILK, BEER, CHIPS
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT
(SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt
ORDER BY CUST
GO
-- Unpivot Table ordered by CUST
SELECT CUST, PRODUCT, QTY
FROM
(
SELECT CUST, VEG, SODA, MILK, BEER, CHIPS
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT
( SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt) p
UNPIVOT
(QTY FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)
)
AS Unpvt
GO
-- Clean up database
DROP TABLE Product
GO

ResultSet:
-- Selecting and checking entires in table
Cust Product QTY
------------------------- -------------------- -----------
KATE VEG 2
KATE SODA 6
KATE MILK 1
KATE BEER 12
FRED MILK 3
FRED BEER 24
KATE VEG 3

-- Pivot Table ordered by PRODUCT
PRODUCT FRED KATE
-------------------- ----------- -----------
BEER 24 12
MILK 3 1
SODA NULL 6
VEG NULL 5

-- Pivot Table ordered by CUST
CUST VEG SODA MILK BEER CHIPS
------------------------- ----------- ----------- ----------- ----------- -----------
FRED NULL NULL 3 24 NULL
KATE 5 6 1 12 NULL

-- Unpivot Table ordered by CUST
CUST PRODUCT QTY
------------------------- -------- -----------
FRED MILK 3
FRED BEER 24
KATE VEG 5
KATE SODA 6
KATE MILK 1
KATE BEER 12
12


You can see in above example where we are using the SUM aggregated functions. SUM adds up values based on column used in the sum function. In our example Kate and Veg has two entries. In our pivot example with order by Cust the values are summed up. Now when table goes under UNPIVOT operations it transforms the table which is already went under PIVOT operation.

Looking at the final PIVOT – UNPIVOT table is little different from the original table and it contains the sum of the two records which we have observed in the PIVOT table. You can see that result which are displayed in red fonts are summed.

This way we can get the original table back if aggregate functions was not applied on the data or data was in such form that aggregate function might have not made any difference.

Reference : Pinal Dave (http://blog.SQLAuthority.com), SQL SERVER – UNPIVOT Table Example, SQL SERVER – PIVOT Table Example

SQL SERVER – UNPIVOT Table Example

My previous article SQL SERVER – PIVOT Table Example encouraged few of my readers to ask me question about UNPIVOT table. UNPIVOT table is reverse of PIVOT Table.

USE AdventureWorks
GO
CREATE TABLE #Pvt ([CA] INT NOT NULL,
[AZ] INT NOT NULL,
[TX] INT NOT NULL);
INSERT INTO #Pvt ([CA], [AZ], [TX])
SELECT [CA], [AZ], [TX]
FROM
(
SELECT sp.StateProvinceCode
FROM Person.Address a
INNER JOIN Person.StateProvince sp
ON a.StateProvinceID = sp.StateProvinceID
) p
PIVOT
(
COUNT (StateProvinceCode)
FOR StateProvinceCode
IN ([CA], [AZ], [TX])
)
AS pvt;
SELECT StateProvinceCode, Customer_Count
FROM
(
SELECT [CA], [AZ], [TX]
FROM #Pvt
) t
UNPIVOT
(
Customer_Count
FOR StateProvinceCode
IN ([CA], [AZ], [TX])
)
AS unpvt;
DROP TABLE #Pvt;
GO

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – PIVOT Table Example

This is quite a popular question and I have never wrote about this on my blog. A Pivot Table can automatically sort, count, and total the data stored in one table or spreadsheet and create a second table displaying the summarized data. The PIVOT operator turns the values of a specified column into column names, effectively rotating a table.
USE AdventureWorks
GO
SELECT [CA], [AZ], [TX]
FROM
(
SELECT sp.StateProvinceCode
FROM Person.Address a
INNER JOIN Person.StateProvince sp
ON a.StateProvinceID = sp.StateProvinceID
) p
PIVOT
(
COUNT (StateProvinceCode)
FOR StateProvinceCode
IN ([CA], [AZ], [TX])
)
AS pvt;


Reference : Pinal Dave (http://blog.SQLAuthority.com)