Merge rows with comma separated values and Sum column in SQL Server

Last Reply 2 months ago By dharmendr

Posted 2 months ago

Hi!

How to get sum by years group

It's my script:

DECLARE @cash TABLE (Id int, smoney money, syear char(7), std char(5), course int, pay int, regdate date)
INSERT INTO @cash VALUES (1, 500, N'2018/19', N'A0002', 2, 0, '2018-09-07')
INSERT INTO @cash VALUES (2, 500, N'2018/19', N'A0003', 2, 0, '2018-09-07')
INSERT INTO @cash VALUES (3, 2500, N'2018/19', N'A0003', 2, 0, '2018-10-07')
INSERT INTO @cash VALUES (4, 2500, N'2018/19', N'A0002', 2, 0, '2018-12-07')
INSERT INTO @cash VALUES (5, 1500, N'2019/20', N'A0001', 3, 0, '2019-10-07')
INSERT INTO @cash VALUES (6, 2000, N'2019/20', N'A0001', 3, 0, '2019-08-07')

SELECT * FROM @cash

;WITH CTE AS(SELECT * FROM @cash)
SELECT  STUFF((SELECT ','+ CONVERT(VARCHAR,LTRIM(RTRIM(t2.Id))) FROM CTE T2 WHERE T2.std = T1.std FOR XML PATH('')),1,1,'') AS Id
	   ,SUM(T1.smoney)SMONEY
	   ,T1.syear
	   ,T1.std
	   ,T1.course
	   ,T1.pay
       ,STUFF((SELECT ','+ CONVERT(VARCHAR,LTRIM(RTRIM(t2.regdate))) FROM CTE T2 WHERE T2.std = T1.std FOR XML PATH('')),1,1,'') AS RegDate
FROM CTE T1
GROUP BY T1.syear, T1.std, T1.regdate, T1.Id, T1.course, T1.pay
ORDER BY T1.course

I couldn’t get below result.

money

syear

course

std

Id

regdate

3000

2018/19

2

A0002

1,4

2018-09-07, 2018-12-07

3000

2018/19

2

A0003

2,3

2018-09-07, 2018-10-07

3500

2019/20

3

A0001

5,6

2019-10-07, 2019-08-07

You are viewing reply posted by: dharmendr 2 months ago.
Posted 2 months ago

Hi PRA,

Refer below query.

SQL

INSERT INTO @cash VALUES (1, 500, N'2018/19', N'A0002', 2, 0, '2018-09-07')
INSERT INTO @cash VALUES (2, 500, N'2018/19', N'A0003', 2, 0, '2018-09-07')
INSERT INTO @cash VALUES (3, 2500, N'2018/19', N'A0003', 2, 0, '2018-10-07')
INSERT INTO @cash VALUES (4, 2500, N'2018/19', N'A0002', 2, 0, '2018-12-07')
INSERT INTO @cash VALUES (5, 1500, N'2019/20', N'A0001', 3, 0, '2019-10-07')
INSERT INTO @cash VALUES (6, 2000, N'2019/20', N'A0001', 3, 0, '2019-08-07')
 
;WITH CTE AS(SELECT * FROM @cash)
SELECT Id,SUM(smoney) SMONEY,syear,std,course,pay,RegDate FROM 
(SELECT  STUFF((SELECT ','+ CONVERT(VARCHAR,LTRIM(RTRIM(t2.Id))) FROM CTE T2 WHERE T2.std = T1.std FOR XML PATH('')),1,1,'') AS Id
       ,T1.smoney
       ,T1.syear
       ,T1.std
       ,T1.course
       ,T1.pay
       ,STUFF((SELECT ','+ CONVERT(VARCHAR,LTRIM(RTRIM(t2.regdate))) FROM CTE T2 WHERE T2.std = T1.std FOR XML PATH('')),1,1,'') AS RegDate
FROM CTE T1
)x
GROUP BY syear,std,regdate,Id,course,pay
ORDER BY course

Output

Id SMONEY syear std course pay RegDate
1,4 3000 2018/19 A0002 2 0 2018-09-07,2018-12-07
2,3 3000 2018/19 A0003 2 0 2018-09-07,2018-10-07
5,6 3500 2019/20 A0001 3 0 2019-10-07,2019-08-07