How to concatenate column values from multiple rows
Table A
FName LName amount Id
---------------------
A B 100 1
A B 200 2
A C 400 3
A B 300 4
I need the result like :
FName LName totalAmount count Id
------------------------------------
A B 600 3 1,2,4
A C 400 1 3
How to get it?
Please help me....
Thanks & Regards
Vineetha..
DECLARE @TABLE_A TABLE
(FName VARCHAR(10), LName VARCHAR(10), amount INT, Id INT)
INSERT INTO @TABLE_A VALUES
('A','B',100,1),
('A','B',200,2),
('A','C',400,3),
('A','B',300,4)
SELECT A.FName
,A.LName
,SUM(A.amount) AS totalAmount
,COUNT(*) AS [COUNT]
,STUFF((
SELECT ',' + CAST(ID AS NVARCHAR(10))
FROM @TABLE_A
WHERE FName = A.FName AND LName = A.LName
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,1,'') AS ID
FROM @TABLE_A A
GROUP BY A.FName
,A.LName
╔═══════╦═══════╦═════════════╦═══════╦══════════╗
║ FName ║ LName ║ totalAmount ║ COUNT ║ ID ║
╠═══════╬═══════╬═════════════╬═══════╬══════════╣
║ A ║ B ║ 600 ║ 3 ║ 1,2,4 ║
║ A ║ C ║ 400 ║ 1 ║ 3 ║
╚═══════╩═══════╩═════════════╩═══════╩══════════╝
SQL FIDDLE
