info@techdevops.com | 437-991-3573 | Data Engineering Services
TechDevOps.com
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance



T-SQL Pivot Data
by BF (Principal Consultant; Architecture; Engineering)
2016-12-17









Data Prep:


Data Prep: Create Orders Table

CREATE TABLE dbo.ORDERS_1
(OrderID INT,
CustomerID INT,
ItemDescription VARCHAR(100),
ItemCost INT,
PurchaseDate DATE NOT NULL)


Data Prep: Populate sample data

INSERT INTO dbo.ORDERS_1 VALUES (1000, 1, 'Simulator1', 5, '2016-12-01')
INSERT INTO dbo.ORDERS_1 VALUES (1001, 1, 'Simulator1', 5, '2016-12-01')
INSERT INTO dbo.ORDERS_1 VALUES (1002, 1, 'Simulator1', 5, '2016-12-01')
INSERT INTO dbo.ORDERS_1 VALUES (1003, 2, 'Simulator1', 5, '2016-12-01')
INSERT INTO dbo.ORDERS_1 VALUES (1004, 2, 'Simulator1', 5, '2016-12-01')
INSERT INTO dbo.ORDERS_1 VALUES (1005, 3, 'Simulator1', 5, '2016-12-02')
INSERT INTO dbo.ORDERS_1 VALUES (1006, 3, 'Simulator1', 5, '2016-12-02')
INSERT INTO dbo.ORDERS_1 VALUES (1007, 3, 'Simulator2', 10, '2016-12-02')
INSERT INTO dbo.ORDERS_1 VALUES (1008, 1, 'Simulator2', 10, '2016-12-02')
INSERT INTO dbo.ORDERS_1 VALUES (1009, 1, 'Simulator2', 10, '2016-12-02')
INSERT INTO dbo.ORDERS_1 VALUES (1010, 3, 'Simulator3', 15, '2016-12-03')
INSERT INTO dbo.ORDERS_1 VALUES (1011, 3, 'Simulator3', 15, '2016-12-03')
INSERT INTO dbo.ORDERS_1 VALUES (1012, 3, 'Simulator3', 15, '2016-12-03')
INSERT INTO dbo.ORDERS_1 VALUES (1014, 1, 'Simulator2', 10, '2016-12-04')
INSERT INTO dbo.ORDERS_1 VALUES (1015, 1, 'Simulator2', 10, '2016-12-04')


Data Prep: View data

SELECT * FROM dbo.ORDERS_1 ORDER BY PurchaseDate ASC, CustomerID ASC




Pivot Query

SELECT
PurchaseDate,
CustomerID,
COALESCE([Simulator1], 0) TotalSimulator1,
COALESCE([Simulator2], 0) TotalSimulator2,
COALESCE([Simulator3], 0) TotalSimulator3
FROM
(SELECT PurchaseDate, CustomerID, ItemDescription, SUM(ItemCost) AS ItemCost FROM dbo.ORDERS_1 GROUP BY PurchaseDate, CustomerID, ItemDescription) o
PIVOT(
SUM(o.ItemCost)
FOR o.ItemDescription IN ([Simulator1],[Simulator2],[Simulator3])
) AS pvt
ORDER BY pvt.PurchaseDate ASC, CustomerID ASC





Note: GroupBy on Inner Select to prepare the data before Pivot/Transform occurs



To do the same via CASE Stmt:

SELECT PurchaseDate, CustomerID,
ISNULL(SUM(CASE ItemDescription WHEN 'Simulator1' THEN ItemCost END),0) AS 'TotalSimulator1',
ISNULL(SUM(CASE ItemDescription WHEN 'Simulator2' THEN ItemCost END),0) AS 'TotalSimulator2',
ISNULL(SUM(CASE ItemDescription WHEN 'Simulator3' THEN ItemCost END),0) AS 'TotalSimulator3'
FROM dbo.ORDERS_1
GROUP BY PurchaseDate, CustomerID
ORDER BY PurchaseDate ASC, CustomerID ASC