CODEWITHSUNDEEP
sqlsql-serversql-server-2014
Gokhan USTUNER
Gokhan USTUNER

Reputation: 21

Concatenate row values into single row per group

I have a problem.

Table-1 Name : InvoiceDetail

InvoiceNo      StockCode       Piece
----------     -----------     ------
1                CP-001          10
1                CP-002          15
2                CP-001          18
2                MN-001          18

Table-2 Name : Stock

StockCode       StockName
-----------     -----------
CP-001          Computer-A
CP-002          Computer-B
MN-001          Monitor-A

Expected Result

InvoiceNo      Description                   TotalPiece
----------     -----------------------       ----------
1              Computer-A, Computer-B            25
2              Computer-A, Monitor-A             36

I wrote the below query

Query

SELECT InvoiceNo, 
       (select StockName + '-' from Stock
    where StockCode = Results.StockCode
    order by StockName 
    FOR XML PATH('')) AS Description,
    SUM(Piece) AS TotalPiece
FROM InvoiceDetail Results
GROUP BY InvoiceNo, Results.StockCode
ORDER BY InvoiceNo

And results

InvoiceNo   Description TotalPiece
1           Computer-A- 10
1           Computer-B- 15
2           Computer-A- 18
2           Monitor-A-  18

Whats wrong?

Upvotes: 1

Views: 322

Answers (1)

sstan
sstan

Reputation: 36473

Perform the group by separately in a CTE, and then you can safely concatenate the description as a separate step:

with InvoiceGroupings as (
  select t.InvoiceNo,
         sum(t.Piece) as TotalPiece
    from InvoiceDetail t
   group by t.InvoiceNo)
select g.InvoiceNo,
       stuff((select ', ' + s.StockName
               from InvoiceDetail i
               join Stock s
                 on i.StockCode = s.StockCode
              where i.InvoiceNo = g.InvoiceNo
              order by s.StockName
                for xml path('')),1,2,'') as Description,
       g.TotalPiece
  from InvoiceGroupings g
 order by g.InvoiceNo

SQL Fiddle Demo

Upvotes: 1

Related Questions