CONCAT and SUM from one dataset in SQL Server 2016

Question

Post reply

CONCAT and SUM from one dataset in SQL Server 2016

Pete Bishop

Default port

Points: 1478
More actions
October 28, 2022 at 1:50 pm

#4105883
Given the sample date:
```
ID    Code       Store    Value
1     PEN        BAJA     10
2     PEN        BAJA     15
3     NOTEBOOK   LA       27
```
Is there a query which will output the data:
```
ID    Code       Store    Value
1,2   PEN        BAJA     25
3     NOTEBOOK   LA       27
```
The values for code and store are unknown in advance (so can't use any solutions which requires this knowledge) and can't use STRING_AGG() since it's not available in SQL 2016.
Thanks

November 3, 2022 at 6:53 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16155 More actions · Answer 1

google for xml path rows to column

in adittion to the above you will also need a group by for the value column

This reply was modified 2 years ago by frederico_fonseca.

Ed B Hall of Fame Points: 3666 More actions · Answer 2

It looks like you will need to group by Code and Store, Sum (Value) and use cross apply select... for...xml path.

I've never been able to write that piece of code without copying it, but here's my copied attempt.

CREATE TABLE #table
(   ID INT, 
    Code VARCHAR(20), 
    Store VARCHAR(20),
    [Value] INT
)

INSERT #table VALUES  
(1, 'PEN', 'BAJA', 10),
(2, 'PEN', 'BAJA', 15),
(3, 'NOTEBOOK', 'LA', 27)

SELECT b.IDList, Code, Store,
        SUM([Value]) AS 'Value'
FROM   #table AS a
CROSS APPLY 
 ( SELECT  STUFF(( SELECT  ', ' + CAST(ID AS VARCHAR(10))
                   FROM   #table
                   WHERE Store = a.Store
                   AND Code = a.Code
                   FOR XML PATH('')
                  ), 1, 2, '') AS IDList
 ) AS b
GROUP BY Code, Store, b.IDList
ORDER BY a.Store

EdVassie SSC Guru Points: 60472 More actions · Answer 3

FOR XML PATH is the only way I know for your version of SQL. The syntax is a bit arcane but you can live on it. It will continue to work in more recent versions but String_agg is more efficient.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara