Group By with concatenate values with sum

Question

I have a table

ReferenceNo |  Description | Amount
--------------------------------
    1222        abcd         30
    1223        efgh         10
    1223        ijkl         10
    1224        mnop         15
    1224        qrst          5
    1225        uvwx         33

I want output like this:

 ReferenceNo |  Description | Amount
 --------------------------------
    1222        abcd         30
    1223        efgh ijkl    20                        
    1224        mnop qrst    20                     
    1225        uvwx         33

here referenceNo are repeated I want to add the amount and need to concatenate description group by referenceNo

Please help me how to solve this problem

@gordon Linoff YES if we have duplicate referenceNo then we need to sum the amount and concatenate description — Chintu, Jun 05 '14 at 13:41
http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string — Michael McGriff, Jun 05 '14 at 13:53

score 3 · Accepted Answer · answered Jun 05 '14 at 13:43

I'm not sure if there's an easier way, but you can do it with a subquery using FOR XML PATH output

SELECT RefernceNo, 
     (SELECT Description + ' ' FROM [table] t2
      WHERE t2.ReferenceNo = t1.ReferenceNo FOR XML PATH('')
     ),
     SUM(Amount)
 FROM [table] as t1
 GROUP BY RefernceNo

Rahul · Answer 2 · 2014-06-05T14:18:36.833

Though it's bit lengthy query but using basic SQL. Use the below query. Making it a 3 part, 1. get the count, 2. get the concatenated value, 3. Get the non duplicated description and finally joining all of them together. See a demo here http://sqlfiddle.com/#!3/10d2d/21.

SELECT REFERENCENO,NEWDESC,Total FROM
(
select X.REFERENCENO,X.NEWDESC,Y.Total,
ROW_NUMBER() OVER(PARTITION BY X.REFERENCENO 
 ORDER BY X.REFERENCENO DESC) as rn
from
(
select distinct t1.ReferenceNo,
t1.Description + t2.Description as NewDesc
from table1 t1
join table1 t2 
on t1.ReferenceNo = t2.ReferenceNo
and t1.Description <> t2.Description

union all

select [ReferenceNo], 
[Description]
from table1
where ReferenceNo in 
(
 select ReferenceNo from table1
group by [ReferenceNo]
having count(ReferenceNo) = 1
 )
) X 
inner join 
(
  select ReferenceNo,
sum(Amount) as Total
from table1
group by ReferenceNo
  ) Y
on X.ReferenceNo = Y.ReferenceNo
) TAB where rn = 1

Group By with concatenate values with sum

2 Answers2