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
|