Convert table data and reformat

I have table named Table

id	Level1	Level2	Level3
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

and I would like to convert it to the (First Conversion) below format which will sum Level2 and column group by Level1 ‘ as below

select Level1, level2, sum(level3) as level3
from table
group by level2
order by level1

Then (Second Conversion) combining the common rows which had the same Level1 to be like below

Answer

You may consider grouping your data and using STRING_AGG as shown below

Conversion 1

The code below groups by Level1 and Level2 and sums Level3 before renaming it to LEVEL3

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

Level1	Level2	Level3
US	CA	37000
FR	PA	30888
US	LA	10000
UK	LN	1100
FR	DF	1000

---

Conversion 2

The query below uses the results from the previous query as a subquery (may also be used as a CTE if desired) and uses the STRING_AGG function to concatenate the grouped results based on LEVEL1. You may additionally order the results if desired.

    
SELECT
    Level1,
    STRING_AGG(Level2,',') as Level2,
    STRING_AGG(Level3,',') as Level3
FROM (
    SELECT
        Level1,
        Level2,
        SUM(Level3) as Level3
    FROM
        my_table
    GROUP BY
        Level1,
        Level2
) t
GROUP BY
    Level1

Level1	Level2	Level3
FR	PA,DF	30888,1000
UK	LN	1100
US	CA,LA	37000,10000

---

Working example on db-fiddle here

Edit 1:

For SQL Server Versions not supporting STRING_AGG, the following approach using JOINs and CROSS APPLY with FOR XML PATH to Concatenate the columns may be used:

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

or simplier

WITH group_1 as (
    SELECT
        Level1,
        Level2,
        CONVERT(VARCHAR(10),SUM(Level3)) as Level3
    FROM
        my_table
    GROUP BY
        Level1,
        Level2
),
level_grouped AS (
    SELECT
        Level1,
        LEFT(Merged2Values,LEN(Merged2Values)-1) AS Level2,
        LEFT(Merged3Values,LEN(Merged3Values)-1) AS Level3
    FROM group_1 extern
    CROSS APPLY (
        SELECT
            Level2+','
        FROM
            group_1 intern
        WHERE
            extern.Level1 = intern.Level1
        FOR XML PATH('')
    )merged2(Merged2Values)
    CROSS APPLY (
        SELECT
            Level3+','
        FROM
            group_1 intern
        WHERE
            extern.Level1 = intern.Level1
        FOR XML PATH('')
    )merged3(Merged3Values)
)
SELECT
    Level1,
    Level2,
    Level3
FROM 
    level_grouped g1
GROUP BY
    Level1,
    Level2,
    Level3

Working db fiddle

Let me know if this works for you