Concatenate row record base on group by in SQL Server 2008

Question

I have one table (tblproduct) with fields: dept, product, qty. sample data below:

dept    product qty
IT  A   2
IT  B   1
PU  C   4
SAL D   1
SER D   2
SER A   4

I want to create stored pro in sql server with the result below:

product qty remark
A   6   IT=2,SER=4
B   1   IT=1
C   4   PU=4
D   3   SAL=1,SER=2

this is my stored pro

select product,
       sum(qty)
from tblproduct
group by product
order by product

Pls. any help. thanks.

Answer 1

SELECT
     [product], SUM(qty) Total_Qty,
     STUFF(
         (SELECT ',' + dept + '=' + CAST(qty AS VARCHAR(10))
          FROM TableName
          WHERE [product] = a.[product]
          FOR XML PATH (''))
          , 1, 1, '')  AS Remark
FROM TableName AS a
GROUP BY [product]

• SQLFiddle Demo

OUTPUT

╔═════════╦═══════════╦═════════════╗
║ PRODUCT ║ TOTAL_QTY ║   REMARK    ║
╠═════════╬═══════════╬═════════════╣
║ A       ║         6 ║ IT=2,SER=4  ║
║ B       ║         1 ║ IT=1        ║
║ C       ║         4 ║ PU=4        ║
║ D       ║         3 ║ SAL=1,SER=2 ║
╚═════════╩═══════════╩═════════════╝

Answer 2

Please try:

SELECT product, SUM(qty) Qty,
    STUFF(
    (SELECT ','+b.dept+'='+CAST(qty as nvarchar(10))
       FROM YourTable b where b.product=a.product
        FOR XML PATH(''),type).value('.','nvarchar(max)'), 1, 1, '')
from YourTable a
group by product