Skip to content
Advertisement

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

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

Table Conversion

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

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.

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:

or simplier

Working db fiddle

Let me know if this works for you

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement