How to insert a new row after the last record of each group in SQL Server-CodePudding

I'm trying to insert a new row after each group in SQL Server.

This is the original table

This is the expected output

The problem what I have here I'm not sure how to mix two of them together in a new view I'm creating.

To get the last record:

WITH new_students_table AS 
(
    SELECT 
        m.*, 
        ROW_NUMBER() OVER (PARTITION BY Subject ORDER BY Subject DESC) AS student
    FROM 
        students AS m
)
SELECT * 
FROM ranked_messages 
WHERE student = 1;

To get the sum:

SELECT 
    [Subject] = COALESCE([Subject], 'Total'), 
    [Score] = SUM([Score])
FROM 
    students
GROUP BY 
    GROUPING SETS(([Subject]), ());

Any help would be greatly appreciated.

CodePudding user response：

This seems to do the trick.

Notice row_number() and the first of the Grouping Sets

Select Subject   = case when StudentID is null Then 'Total ' Subject else Subject end
      ,StudentID 
      ,Score=sum(Score)
 From  (Select *
              ,RN=row_number() over (partition by Subject order by StudentID)
         From  YourTable 
       ) src
 GROUP BY GROUPING SETS( ([Subject],[StudentID],[RN])
                        ,([Subject])
                        );

Results