By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;CREATE TABLE Data (
Country VARCHAR(100),
sales_date DATE,
sales_code VARCHAR(100),
type_item VARCHAR(100),
sales INT
)
INSERT Data
VALUES
('Algeria', '2024-06-01', 'ACTUAL', 'Hat', 200),
('Algeria', '2024-06-01', 'ACTUAL', 'Shirt', 145),
('Algeria', '2024-06-01', 'ACTUAL', 'Pant', 150),
('Algeria', '2024-06-01', 'TARGET', 'Hat', 90),
('Algeria', '2024-06-01', 'TARGET', 'Shirt', 500),
('Algeria', '2024-06-01', 'TARGET', 'Pant', 100)
| (No column name) |
|---|
| Microsoft SQL Server 2017 (RTM-CU30) (KB5013756) - 14.0.3451.2 (X64) Jun 22 2022 18:20:15 Copyright (C) 2017 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) |
-- Using a compound pivot axis PIVOT
SELECT PVT.*
FROM (
SELECT
Country, sales_date, sales,
CONCAT(type_item, '_sales_', sales_code) AS Combined
FROM Data
) D
PIVOT (
SUM(sales)
FOR Combined IN (
hat_sales_ACTUAL, hat_sales_TARGET,
shirt_sales_ACTUAL, shirt_sales_TARGET,
pant_sales_ACTUAL, pant_sales_TARGET
)
) PVT
| Country | sales_date | hat_sales_ACTUAL | hat_sales_TARGET | shirt_sales_ACTUAL | shirt_sales_TARGET | pant_sales_ACTUAL | pant_sales_TARGET |
|---|---|---|---|---|---|---|---|
| Algeria | 2024-06-01 | 200 | 90 | 145 | 500 | 150 | 100 |
-- Using COnditional Aggregation
SELECT
Country, sales_date,
SUM(CASE WHEN sales_code = 'ACTUAL' AND type_item = 'Hat' THEN sales ELSE 0 END)
AS hat_sales_ACTUAL,
SUM(CASE WHEN sales_code = 'TARGET' AND type_item = 'Hat' THEN sales ELSE 0 END)
AS hat_sales_TARGET,
SUM(CASE WHEN sales_code = 'ACTUAL' AND type_item = 'Shirt' THEN sales ELSE 0 END)
AS shirt_sales_ACTUAL,
SUM(CASE WHEN sales_code = 'TARGET' AND type_item = 'Shirt' THEN sales ELSE 0 END)
AS shirt_sales_TARGET,
SUM(CASE WHEN sales_code = 'ACTUAL' AND type_item = 'Pant' THEN sales ELSE 0 END)
AS pant_sales_ACTUAL,
SUM(CASE WHEN sales_code = 'TARGET' AND type_item = 'Pant' THEN sales ELSE 0 END)
AS pant_sales_TARGET
FROM Data
GROUP BY Country, sales_date
| Country | sales_date | hat_sales_ACTUAL | hat_sales_TARGET | shirt_sales_ACTUAL | shirt_sales_TARGET | pant_sales_ACTUAL | pant_sales_TARGET |
|---|---|---|---|---|---|---|---|
| Algeria | 2024-06-01 | 200 | 90 | 145 | 500 | 150 | 100 |
-- Using dynamic SQL (SQL Server 2017 and later)
DECLARE @PivotForInList NVARCHAR(MAX) = (
SELECT STRING_AGG(QUOTENAME(CONCAT(type_item, '_sales_', sales_code)), ', ')
WITHIN GROUP(ORDER BY type_item, sales_code)
FROM (SELECT DISTINCT type_item FROM Data) D1
CROSS JOIN (SELECT DISTINCT sales_code FROM Data) D2
)
DECLARE @Sql NVARCHAR(MAX) = '
SELECT PVT.*
FROM (
SELECT
Country, sales_date, sales,
CONCAT(type_item, ''_sales_'', sales_code) AS Combined
FROM Data
) D
PIVOT (
SUM(sales)
FOR Combined IN (' + @PivotForInList + ')
) PVT
'
PRINT @Sql
EXEC (@Sql)
| Country | sales_date | Hat_sales_ACTUAL | Hat_sales_TARGET | Pant_sales_ACTUAL | Pant_sales_TARGET | Shirt_sales_ACTUAL | Shirt_sales_TARGET |
|---|---|---|---|---|---|---|---|
| Algeria | 2024-06-01 | 200 | 90 | 150 | 100 | 145 | 500 |
SELECT PVT.*
FROM (
SELECT
Country, sales_date, sales,
CONCAT(type_item, '_sales_', sales_code) AS Combined
FROM Data
) D
PIVOT (
SUM(sales)
FOR Combined IN ([Hat_sales_ACTUAL], [Hat_sales_TARGET], [Pant_sales_ACTUAL], [Pant_sales_TARGET], [Shirt_sales_ACTUAL], [Shirt_sales_TARGET])
) PVT
-- Using dynamic SQL (Older SQL Server versions)
-- Uses teh FOR XML technique as a substitute for STRING_AGG()
DECLARE @PivotForInList NVARCHAR(MAX) = STUFF((
SELECT ', ' + QUOTENAME(CONCAT(type_item, '_sales_', sales_code))
FROM (SELECT DISTINCT type_item FROM Data) D1
CROSS JOIN (SELECT DISTINCT sales_code FROM Data) D2
ORDER BY type_item, sales_code
FOR XML PATH(''), TYPE
).value('.', 'varchar(max)'), 1, 2, '')
DECLARE @Sql NVARCHAR(MAX) = '
SELECT PVT.*
FROM (
SELECT
Country, sales_date, sales,
CONCAT(type_item, ''_sales_'', sales_code) AS Combined
FROM Data
) D
PIVOT (
SUM(sales)
FOR Combined IN (' + @PivotForInList + ')
) PVT
'
PRINT @Sql
EXEC (@Sql)
| Country | sales_date | Hat_sales_ACTUAL | Hat_sales_TARGET | Pant_sales_ACTUAL | Pant_sales_TARGET | Shirt_sales_ACTUAL | Shirt_sales_TARGET |
|---|---|---|---|---|---|---|---|
| Algeria | 2024-06-01 | 200 | 90 | 150 | 100 | 145 | 500 |
SELECT PVT.*
FROM (
SELECT
Country, sales_date, sales,
CONCAT(type_item, '_sales_', sales_code) AS Combined
FROM Data
) D
PIVOT (
SUM(sales)
FOR Combined IN ([Hat_sales_ACTUAL], [Hat_sales_TARGET], [Pant_sales_ACTUAL], [Pant_sales_TARGET], [Shirt_sales_ACTUAL], [Shirt_sales_TARGET])
) PVT