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

Last Reply 10 months ago By dharmendr

Posted 10 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 10 months ago.
Posted 10 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