Hi ramco1917,
Yes you can use RowNumber function.
Refer below example. I have used the CASE statement and calculate the total for each group.
SQL
DECLARE @Test AS TABLE (StcCode INT, DocDate VARCHAR(20), BpGSTN NUMERIC(18,2))
INSERT INTO @Test VALUES(19137, 'CSGST-18', 13525.2)
INSERT INTO @Test VALUES(19137, 'CSGST-18', 13525.2)
INSERT INTO @Test VALUES(19137, 'CSGST-18', 384.95)
INSERT INTO @Test VALUES(19137, 'CSGST-18', 384.95)
INSERT INTO @Test VALUES(19138, 'CSGST-18', 164.52)
INSERT INTO @Test VALUES(19138, 'CSGST-18', 164.52)
;WITH CTE AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY StcCode ORDER BY StcCode) [RowNumber],
StcCode, DocDate, BpGSTN
FROM @Test
)
SELECT StcCode, DocDate, BpGSTN,
CASE WHEN [RowNumber] = 1
THEN CAST((SELECT SUM(BpGSTN) FROM @Test WHERE BpGSTN = CTE.BpGSTN) AS VARCHAR(20))
ELSE '' END [Total]
FROM CTE
Screenshot
