SQL Server Pivot Table Example

The SQL Server pivot function allows you to visualize data comparisons, patterns, and trends by representing the data in a calculated, condensed, and analytical manner.

The information will be presented as grouped values, which combine the different elements of a larger table into one or more distinct categories. This summary may contain sums, averages, or other data that the pivot table aggregates using an aggregation function of choice that is applied to the grouped information.

Let’s see how it functions using a real-world example.

DROP TABLE IF EXISTS SalesData

CREATE TABLE SalesData (SalesDataID INT, TrxnID INT, SoldDate DATETIME, dcAmount DECIMAL(38,2))

INSERT INTO SalesData VALUES (1, 1001, ’05/01/2009′, 101.00)
INSERT INTO SalesData VALUES (1, 1001, ’05/15/2009′, 102.00)
INSERT INTO SalesData VALUES (1, 1001, ’05/20/2009′, 105.00)
INSERT INTO SalesData VALUES (2, 1001, ’05/01/2009′, 41.00)
INSERT INTO SalesData VALUES (2, 1001, ’05/15/2009′, 44.00)
INSERT INTO SalesData VALUES (3, 1001, ’06/01/2009′, 330.00)
INSERT INTO SalesData VALUES (4, 1001, ’07/01/2009′, 100.00)

INSERT INTO SalesData VALUES (1, 1002, ’05/01/2009′, 101.00)
INSERT INTO SalesData VALUES (1, 1002, ’05/15/2009′, 102.00)
INSERT INTO SalesData VALUES (1, 1002, ’05/20/2009′, 105.00)
INSERT INTO SalesData VALUES (2, 1002, ’05/01/2009′, 41.00)
INSERT INTO SalesData VALUES (2, 1002, ’05/15/2009′, 44.00)
INSERT INTO SalesData VALUES (3, 1002, ’06/01/2009′, 330.00)

SELECT * FROM SalesData

SELECT TrxnID ,[05/01/2009] ,[05/15/2009] ,[05/20/2009] ,[06/01/2009],[07/01/2009]
FROM (
SELECT TrxnID, SoldDate, dcAmount
FROM SalesData) up
PIVOT ( SUM(dcAmount) FOR SoldDate in ([05/01/2009] ,[05/15/2009] ,[05/20/2009] ,[06/01/2009],[07/01/2009]) )AS pvt