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

Hope you find this article helpful.

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s