SQL Server 2014

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;


(No column name)


Microsoft SQL Server 2014 (SP3-CU-GDR) (KB4535288) - 12.0.6372.1 (X64) 
	Dec 12 2019 15:14:11 
	Copyright (c) Microsoft Corporation
	Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE my_table (
  id INTEGER,
  Level1 VARCHAR(2),
  Level2 VARCHAR(2),
  Level3 INTEGER
);

INSERT INTO my_table
  (id, Level1, Level2, Level3)
VALUES
  ('1', 'US', 'CA', '13000'),
  ('2', 'FR', 'PA', '30000'),
  ('3', 'US', 'CA', '24000'),
  ('4', 'US', 'LA', '10000'),
  ('5', 'UK', 'LN', '500'),
  ('6', 'UK', 'LN', '600'),
  ('7', 'FR', 'PA', '888'),
  ('8', 'FR', 'DF', '1000');
8 rows affected

SELECT
    Level1,
    Level2,
    SUM(Level3) as Level3
FROM
    my_table
GROUP BY
    Level1,
    Level2
ORDER BY
    Level1;
    


Level1
Level2
Level3


FR
DF
1000

FR
PA
30888

UK
LN
1100

US
CA
37000

US
LA
10000


WITH group_1 as (
    SELECT
        Level1,
        Level2,
        CONVERT(VARCHAR(10),SUM(Level3)) as Level3
    FROM
        my_table
    GROUP BY
        Level1,
        Level2
),
level2_grouped AS (
    SELECT
        Level1,
        LEFT(MergedValues,LEN(MergedValues)-1) AS Level2
    FROM group_1 extern
    CROSS APPLY (
        SELECT
            Level2+','
        FROM
            group_1 intern
        WHERE
            extern.Level1 = intern.Level1
        FOR XML PATH('')
    )merged(MergedValues)
),
level3_grouped AS (
    SELECT
        Level1,
        LEFT(MergedValues,LEN(MergedValues)-1) AS Level3
    FROM group_1 extern
    CROSS APPLY (
        SELECT
            Level3+','
        FROM
            group_1 intern
        WHERE
            extern.Level1 = intern.Level1
        FOR XML PATH('')
    )merged(MergedValues)
)
SELECT
    g1.Level1,
    l2.Level2,
    l3.Level3
FROM 
    group_1 g1
INNER JOIN 
    level2_grouped l2 ON g1.Level1 = l2.Level1
INNER JOIN 
    level3_grouped l3 ON g1.Level1 = l3.Level1
GROUP BY
    g1.Level1,
    l2.Level2,
    l3.Level3


Level1
Level2
Level3


FR
DF,PA
1000,30888

UK
LN
1100

US
CA,LA
37000,10000