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