By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE prod_sales
(saleID int identity(1,1),
saleDate date,
department varchar(125),
amount int )
INSERT INTO prod_sales VALUES
('2016-01-01','Men''s Clothing & Accessories' ,ROUND(RAND() * 1000, 0)),
('2016-01-02','Phones & Telecommunications',ROUND(RAND() * 1000, 0)),
('2016-01-01','Computer & Office',ROUND(RAND() * 1000, 0)),
('2016-01-02','Consumer Electronics',ROUND(RAND() * 1000, 0)),
('2016-01-01','Men''s Clothing & Accessories' ,ROUND(RAND() * 1000, 0)),
('2016-01-02','Phones & Telecommunications',ROUND(RAND() * 1000, 0)),
('2016-01-01','Computer & Office',ROUND(RAND() * 1000, 0)),
('2016-01-01','Consumer Electronics',ROUND(RAND() * 1000, 0)),
('2016-01-01','Men''s Clothing Accessories' ,ROUND(RAND() * 1000, 0)),
('2016-01-02','Phones & Telecommunications',ROUND(RAND() * 1000, 0)),
('2016-01-02','Computer & Office',ROUND(RAND() * 1000, 0)),
('2016-01-01','Consumer Electronics',ROUND(RAND() * 1000, 0))
SELECT * FROM prod_sales
| saleID | saleDate | department | amount |
|---|---|---|---|
| 1 | 2016-01-01 | Men's Clothing & Accessories | 754 |
| 2 | 2016-01-02 | Phones & Telecommunications | 216 |
| 3 | 2016-01-01 | Computer & Office | 779 |
| 4 | 2016-01-02 | Consumer Electronics | 267 |
| 5 | 2016-01-01 | Men's Clothing & Accessories | 723 |
| 6 | 2016-01-02 | Phones & Telecommunications | 363 |
| 7 | 2016-01-01 | Computer & Office | 109 |
| 8 | 2016-01-01 | Consumer Electronics | 965 |
| 9 | 2016-01-01 | Men's Clothing Accessories | 545 |
| 10 | 2016-01-02 | Phones & Telecommunications | 679 |
| 11 | 2016-01-02 | Computer & Office | 827 |
| 12 | 2016-01-01 | Consumer Electronics | 176 |
CREATE OR ALTER PROCEDURE DynamicPivot
@ColumnToPivot VARCHAR(4000),
@ListToPivot VARCHAR(4000)
AS
BEGIN
DECLARE @SqlStatement VARCHAR(MAX)
SET @SqlStatement =
'SELECT saleDate,' + @ListToPivot + '
FROM
(
SELECT saleDate,department,amount
FROM prod_sales
) AS DataSource
PIVOT
(
SUM(amount)
FOR ['+@ColumnToPivot+'] IN ('+@ListToPivot+')
) AS Pivoting'
EXECUTE (@SqlStatement)
END
EXECUTE DynamicPivot 'department','[Computer & Office],[Consumer Electronics],[Men''s Clothing & Accessories],[Phones & Telecommunications]'
| saleDate | Computer & Office | Consumer Electronics | Men's Clothing & Accessories | Phones & Telecommunications |
|---|---|---|---|---|
| 2016-01-01 | 888 | 1141 | 1477 | null |
| 2016-01-02 | 827 | 267 | null | 1258 |