SQL Server 2017

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