Merge rows with comma separated values in SQL Server

Last Reply 2 months ago By dharmendr

Posted 2 months ago

Hi!

How to get sum by multiple id

I used below 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 money,syear,course,std,Id,regdate
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY (std) ORDER BY syear DESC) Row_No,
    CASE pay WHEN 0 THEN SUM(smoney) END 'money', syear, course, std, Id, regdate
    FROM @cash
    WHERE syear IN (SELECT DISTINCT MAX(syear) FROM @cash GROUP BY std,course) AND pay = 0
    GROUP BY syear, std, course, pay, Id, regdate
)t
WHERE Row_No = 1
ORDER BY course

Possible get below result or not possible?

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

Posted 2 months ago