SQL Server, the
PIVOT
and
UNPIVOT
operators are used to transform data from rows into columns (PIVOT) or from columns into rows (UNPIVOT).
Here's an example of how you can use PIVOT
:
Let's say you have a table Sales
with the following columns: Product
, Month
, and Revenue
. You want to pivot the data to show total revenue for each product across different months.
-- Sample data
CREATE TABLE Sales (
Product VARCHAR(50),
Month VARCHAR(50),
Revenue DECIMAL(10, 2)
);
INSERT INTO Sales (Product, Month, Revenue)
VALUES
('Product_A', 'January', 1000),
('Product_A', 'February', 1500),
('Product_A', 'March', 2000),
('Product_B', 'January', 1200),
('Product_B', 'February', 1600),
('Product_B', 'March', 1800);
-- Pivot query
SELECT *
FROM (
SELECT Product, Month, Revenue
FROM Sales
) AS SourceTable
PIVOT (
SUM(Revenue)
FOR Month IN ([January], [February], [March])
) AS PivotTable;
This query will pivot the data to show total revenue for each product across different months.
And here's an example of how you can use UNPIVOT
:
Let's say you have a table Sales_Pivot
with the following columns: Product
, January
, February
, March
, and you want to unpivot this data to show the revenue for each product in each month.
CREATE TABLE Sales_Pivot (
Product VARCHAR(50),
January DECIMAL(10, 2),
February DECIMAL(10, 2),
March DECIMAL(10, 2)
);
INSERT INTO Sales_Pivot (Product, January, February, March)
VALUES
('Product_A', 1000, 1500, 2000),
('Product_B', 1200, 1600, 1800);
SELECT Product, Month, Revenue
FROM (
SELECT Product, January, February, March
FROM Sales_Pivot
) AS SourceTable
UNPIVOT (
Revenue FOR Month IN ([January], [February], [March])
) AS UnpivotTable;
This query will unpivot the data to show the revenue for each product in each month
Dynamic SQL example
Dynamic SQL in SQL Server allows you to construct SQL statements at runtime, which can be useful for cases where the structure of your pivot or unpivot operation may change dynamically. Here's an example of using dynamic SQL with PIVOT
and UNPIVOT
:
First, let's start with a dynamic pivot example:
CREATE TABLE Sales (
Product VARCHAR(50),
Month VARCHAR(50),
Revenue DECIMAL(10, 2)
);
INSERT INTO Sales (Product, Month, Revenue)
VALUES
('Product_A', 'January', 1000),
('Product_A', 'February', 1500),
('Product_A', 'March', 2000),
('Product_B', 'January', 1200),
('Product_B', 'February', 1600),
('Product_B', 'March', 1800);
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SELECT @cols = STUFF((SELECT distinct ',' + QUOTENAME(Month)
FROM Sales
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @query = 'SELECT Product, ' + @cols + '
FROM
(
SELECT Product, Month, Revenue
FROM Sales
) x
PIVOT
(
SUM(Revenue)
FOR Month IN (' + @cols + ')
) p '
EXEC sp_executesql @query;
This dynamic pivot query will pivot the data to show total revenue for each product across different months.
Now, let's see an example of dynamic unpivot:
CREATE TABLE Sales_Pivot (
Product VARCHAR(50),
January DECIMAL(10, 2),
February DECIMAL(10, 2),
March DECIMAL(10, 2)
);
INSERT INTO Sales_Pivot (Product, January, February, March)
VALUES
('Product_A', 1000, 1500, 2000),
('Product_B', 1200, 1600, 1800);
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SELECT @cols = STUFF((SELECT distinct ',' + QUOTENAME(Month)
FROM (VALUES ('January'), ('February'), ('March')) AS Months(Month)
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @query = 'SELECT Product, Month, Revenue
FROM
(
SELECT Product, January, February, March
FROM Sales_Pivot
) x
UNPIVOT
(
Revenue FOR Month IN (' + @cols + ')
) unpvt'
EXEC sp_executesql @query;
This dynamic unpivot query will unpivot the data to show the revenue for each product in each month.