Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

PIVOT and UNPIVOT Operators in SQL Server

Author: Richard Wand

This article will explain that what are PIVOT and UNPIVOT operators and how we can use these operators in our query in SQL Server.

PIVOT and UNPIVOT relational operators are introduced in SQL Server 2005. These operators are used to create powerful crosstab queries that are used to create crosstab reports. PIVOT and UNPIVOT are used to change values of a table-valued expression to the columns of another table. More often than not we achieve this task by using case statements but PIVOT and UNPOT made easy for us if we use it properly.

PIVOT

PIVOT is a technique which is used to swap the rows of a table into columns. PIVOT interchange values from one column of a table to multiple columns of another table and performs aggregation where required. PIVOT operator is specially used to display summarized and aggregated data in a report. It turns the unique values of a specified column into another table columns.

UNPIVOT

UNPIVOT performs the completely opposite operation, to some extent, to PIVOT by rotating columns of a table-valued expression into column values. The syntax is the same except the use of UNPIVOT in place of PIVOT and it. This can also be perceived as renormalization of the table which we have changed by using PIVOT.

-- creating table
CREATE TABLE #ProductTraffic
(    
    StoreName        VARCHAR(30),
    ProductName     VARCHAR(50),
    Traffic                 INT
)

GO

-- Inserting Data into Table

INSERT INTO #ProductTraffic
    (StoreName, ProductName, Traffic)
VALUES
   ('Walmart','Camera',3)
INSERT INTO #ProductTraffic
   (StoreName, ProductName, Traffic)
VALUES
   ('Amazon','Laptop',11)
INSERT INTO #ProductTraffic
   (StoreName, ProductName, Traffic)
VALUES
   ('Overstock','Camera',4)
INSERT INTO #ProductTraffic
   (StoreName, ProductName, Traffic)
VALUES
   ('Amazon','Tablet',15)
INSERT INTO #ProductTraffic
   (StoreName, ProductName, Traffic)
VALUES
  ('Walmart','Laptop',9)
INSERT INTO #ProductTraffic
   (StoreName, ProductName, Traffic)
VALUES
   ('Overstock','Tablet',17)
INSERT INTO #ProductTraffic
   (StoreName, ProductName, Traffic)
VALUES
   ('Amazon','Camera',7)
INSERT INTO #ProductTraffic
   (StoreName, ProductName, Traffic)
VALUES
  ('Walmart','Tablet',14)
INSERT INTO #ProductTraffic
   (StoreName, ProductName, Traffic)
VALUES
   ('Overstock','Laptop',21)

GO

-- Selecting all records from the table
SELECT    *
FROM        #ProductTraffic

GO

-- using Pivot ORDER BY StoreName

SELECT StoreName, Camera, Laptop, Tablet
FROM
(
    SELECT StoreName, ProductName, Traffic
    FROM #ProductTraffic) up
    PIVOT (SUM(Traffic) FOR ProductName IN (Camera, Laptop, Tablet)
) AS TempPivot
ORDER BY StoreName

GO

-- using PIVOT order by ProductName
SELECT ProductName, Walmart, Amazon, Overstock
FROM
(
    SELECT StoreName, ProductName, Traffic
    FROM #ProductTraffic) up
    PIVOT (SUM(Traffic) FOR StoreName IN (Walmart, Amazon, Overstock)
) AS TempPivot
ORDER BY ProductName

GO

-- using UNPIVOT ORDER BY StoreName
SELECT StoreName, ProductName, Traffic
FROM
(
    SELECT StoreName, Camera, Laptop, Tablet
    FROM
    (
        SELECT StoreName, ProductName, Traffic
        FROM #ProductTraffic
    ) Temp1
PIVOT( SUM(Traffic) FOR ProductName IN (Camera, Laptop, Tablet)) AS TempPivot) Temp2
UNPIVOT(Traffic FOR ProductName IN (Camera, Laptop, Tablet)) AS TempUnpivot

GO
-- Droping TABLE
DROP TABLE #ProductTraffic
GO

 

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< What are the views and how to create a view in SQL Server

Disclaimer - Privacy
© 2002-2017 DevASP.net