SQL Server how to manage rows that have same value in two column, sum few columns , Comma seperate another and display 1 row

Question

I have the following table:

There are total 11 rows. Row 5&6 have the same SenderName and DateOfBirth. I want to display a total 10 rows and merge row 5 & 6 in following manner: Receivers and NoofTransactions columns are being summed.

I have created the test data in following manner.

DECLARE @SenToRecList TABLE
                      (
                          NoofTransactions int, 
                          Sender varchar(50), 
                          SenderName varchar(200), 
                          Receivers int, 
                          Vol money, 
                          DateOfBirth varchar(50)
                      )

INSERT INTO @SenToRecList
VALUES (34, 50116, 'JACQUELINE MONROE' ,33, 2045.0000,'Jan  1 1988 12:00AM')

INSERT INTO @SenToRecList
VALUES (30, 43377, 'Suranjan rai' ,30, 427.7600,'Oct 27 1971 12:00AM')

INSERT INTO @SenToRecList
VALUES (121, 44158 ,'Dev Bhattarai', 27, 3051.3100,'Oct 27 1971 12:00AM')

INSERT INTO @SenToRecList
VALUES (63 ,44205 ,'Retry Hauaja' ,27, 1003.4500,'Oct 27 1971 12:00AM')

INSERT INTO @SenToRecList
VALUES (45, 44343, 'Kessie Frost' ,25 ,694.4200,'Oct 27 1971 12:00AM')

INSERT INTO @SenToRecList
VALUES (45, 443436, 'Kessie Frost' ,25 ,694.4200,'Oct 27 1971 12:00AM')

INSERT INTO @SenToRecList
VALUES (43, 43236, 'Nam Dosan' ,24 ,1340.6700,'Oct 27 1971 12:00AM')

INSERT INTO @SenToRecList
VALUES (47, 44466, 'Micah Bates', 22 ,589.9200,'Oct 27 1971 12:00AM')

INSERT INTO @SenToRecList
VALUES (22, 50094, 'RUTH LEWIS', 22 ,632.6000,'Oct 27 1971 12:00AM')

INSERT INTO @SenToRecList
VALUES (26, 40352, 'aliyan marki', 22, 2657.0000,'Oct 27 1971 12:00AM')

INSERT INTO @SenToRecList
VALUES (176 ,41604 ,'verified SMITH' ,22, 5511.2000,'Oct 27 1971 12:00AM')

Please help me create this query.

Deniz · Accepted Answer · 2021-11-18T17:27:14.280

You can use this query to achieve your goal. (SQL Server 2017 (14.x) and later)

SELECT SenderName, SUM(NoofTransactions) AS NoofTransactions, STRING_AGG(Sender, ',') AS Sender, SUM(Receivers) AS Receivers, SUM(Vol) AS Vol, DateOfBirth
FROM @SenToRecList
GROUP BY SenderName, DateOfBirth

For further reading; https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15

For SQL server pre-2017 check this link; String_agg for SQL Server before 2017

SQL Server how to manage rows that have same value in two column, sum few columns , Comma seperate another and display 1 row

1 Answers1