info@techdevops.com
|
437-991-3573
|
Data Engineering Services
|
TechDevOps.com
|
| | | |
|
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
|
|
|
|
|
|
|
|
|
|