Hi smile,
Refer below test query.
SQL
DECLARE @Test AS TABLE(AdmissionNo CHAR(5),Recievable INT,Arrears INT,NetBal INT,Paid INT,RemBal INT,FeesMonth CHAR(3))
INSERT INTO @Test VALUES('R-001',700,0,700,500,200,'Aug')
INSERT INTO @Test VALUES('R-001',0,200,200,100,100,'Aug')
INSERT INTO @Test VALUES('R-001',500,0,500,0,0,'Sep')
SELECT DISTINCT AdmissionNo,
(SELECT TOP 1 CAST(RemBal AS VARCHAR(20)) FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RowNum,* FROM @Test) t where FeesMonth = 'Aug' ORDER BY t.RowNum DESC)
+ ' + ' + (SELECT TOP 1 CAST(NetBal AS VARCHAR(20)) FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RowNum,* FROM @Test) t where FeesMonth = 'Sep' ORDER BY t.RowNum ASC) 'NetBal',
(SELECT TOP 1 Paid FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RowNum,* FROM @Test) t where FeesMonth = 'Sep' ORDER BY t.RowNum DESC) 'Paid',
(SELECT TOP 1 RemBal FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RowNum,* FROM @Test) t where FeesMonth = 'Aug' ORDER BY t.RowNum DESC)
+ (SELECT TOP 1 NetBal FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RowNum,* FROM @Test) t where FeesMonth = 'Sep' ORDER BY t.RowNum ASC) 'RemBal'
FROM @Test
Output
| AdmissionNo |
NetBal |
Paid |
RemBal |
| R-001 |
100 + 500 |
0 |
600 |
