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
Advertisement
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
Let me know if this works for you