How to do a SQL Query that will combine strings

Question

I have a MSSQL database table with data like:

----------------------------------------
| Id | PartNo | Price  | Qty| ExtPrice |
----------------------------------------
| 1  | Thing1  | 1.50  |  2 |  3.00    |
----------------------------------------
| 2  | Thing 2 | 2.50  |  3 | 7.50     |
----------------------------------------

The last three fields I can do a SUM() with my GROUP BY, But I need it to combine the PartNo Field like this:

--------------------------------------------
| PartNo          | Price | Qty | ExtPrice |
--------------------------------------------
| Thing1, Thing2  | 4.00  |  5  |  10.50   |
--------------------------------------------

M.Ali · Accepted Answer · 2018-08-09 22:24:01Z

3

If you are using any version older than SQL Server 2017 STRING_AGG() will not be available for you to use, for older version you can use the FOR XML PATH('') method to concatenate the strings, something like this...

SELECT STUFF((  SELECT ',' + PartNo
                FROM TableName 
                FOR XML PATH(''), TYPE)
                .value('.', 'NVARCHAR(MAX)') ,1 ,1,'') AS PartNo
      , SUM(Price)      AS Price
      , SUM(Qty)        AS Qty
      , SUM(ExtPrice)   AS ExtPrice
FROM TableName

answered Aug 9, 2018 at 22:24

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Olivier Jacot-Descombes · Accepted Answer · 2018-08-09 22:22:48Z

0

You can use the STRING_AGG (Transact-SQL) aggregate function.

SELECT 
    STRING_AGG (PartNo, ', ') WITHIN GROUP (ORDER BY PartNo ASC) AS PartNo,
    SUM(Price) As Price, 
    SUM(Qty) As Qty, 
    SUM(ExtPrice) As ExtPrice
FROM MyTable;

See: http://sqlfiddle.com/#!18/77ff1/2/0

Btw.: There is no more column left to group by, so, you can just drop the GROUP BY.

edited Aug 9, 2018 at 22:22

answered Aug 9, 2018 at 22:15

Olivier Jacot-Descombes

114k14 gold badges149 silver badges202 bronze badges

Collectives™ on Stack Overflow

How to do a SQL Query that will combine strings

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related