Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Sql select group by and string concat [duplicate]

i have a table like this

ID  NAME    Amount
1   cal     100
2   cal     200
3   cal     300
4   cal     400
1   ser     500
2   ser     600
5   ser     700

i want to write a select query so that i wil get the resul like this

ID  NAME            Amount
1   cal and ser     600
2   cal and ser     800
3   cal             300
4   cal             400
5   ser             700

here i need to group by id and sum of amount and concat the string name with same id and differnet name

like image 269
Girish K G Avatar asked Apr 10 '12 06:04

Girish K G


2 Answers

this will work with sql-server 2008

SELECT p1.ID,
       ( SELECT NAME + ' and ' 
           FROM YourTable  p2
          WHERE p2.ID = p1.ID
          ORDER BY NAME
            FOR XML PATH('') ) AS Name,
        sum(Amount)
      FROM YourTable p1
      GROUP BY ID ;
like image 197
Vikram Avatar answered Sep 23 '22 04:09

Vikram


SELECT p1.ID,  
   STUFF(( SELECT ' and ' + NAME  
       FROM YourTable  p2  
      WHERE p2.ID = p1.ID  
      ORDER BY NAME  
        FOR XML PATH('') )  
    , 1, 5, '' ) AS Name,  
    sum(Amount)  
  FROM YourTable p1  
  GROUP BY ID  

From Vikram answer
I add stuff for my result.

like image 32
HATCHA Avatar answered Sep 20 '22 04:09

HATCHA