Calculate Group Total using Pivot with multiple columns in SQL Server

Hi smile,

Refer below sample query.

SQL

CREATE TABLE tblSetMarks(AdmissionNo varchar(10),Subject varchar(10),Mark int,Total int)
 
INSERT INTO tblSetMarks VALUES('R-1','Eng',23,50)
INSERT INTO tblSetMarks VALUES('R-2','Eng',21,50)
INSERT INTO tblSetMarks VALUES('R-3','Eng',12,50)
INSERT INTO tblSetMarks VALUES('R-4','Eng',29,50)
INSERT INTO tblSetMarks VALUES('R-1','Urdu',23,50)
INSERT INTO tblSetMarks VALUES('R-2','Urdu',21,50)
INSERT INTO tblSetMarks VALUES('R-3','Urdu',12,50)
INSERT INTO tblSetMarks VALUES('R-4','Urdu',29,50)
INSERT INTO tblSetMarks VALUES('R-1','Math',23,50)
INSERT INTO tblSetMarks VALUES('R-2','Math',21,50)
INSERT INTO tblSetMarks VALUES('R-3','Math',12,50)
INSERT INTO tblSetMarks VALUES('R-4','Math',29,50)
 
DECLARE @cols AS NVARCHAR(MAX),@query  AS NVARCHAR(MAX),@colSum AS NVARCHAR(MAX),@colTotal AS NVARCHAR(MAX)
 
select @cols = STUFF((SELECT ',' + QUOTENAME(Subject+'_'+col)
                    from tblSetMarks t
                    cross apply
                    (
                        select 'Mark', 1 union all
                        select 'Total', 2
                    ) c (col, so)
                    group by col, so, Subject
                    order by  Subject, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')
select @colSum = 'SUM('+STUFF((SELECT '+' +  QUOTENAME(Subject+'_'+col) 
                    from tblSetMarks t
                    cross apply
                    (
                        select 'Mark', 1
                    ) c (col, so)
                    group by col, so, Subject
                    order by  Subject, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')+')'

select @colTotal = 'SUM('+STUFF((SELECT '+' +  QUOTENAME(Subject+'_'+col) 
                    from tblSetMarks t
                    cross apply
                    (
                        select 'Total', 1
                    ) c (col, so)
                    group by col, so, Subject
                    order by  Subject, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')+')'
		PRINT @colTotal
set @query = 'SELECT AdmissionNo,' + @cols + ','+@colSum+' AS Obtain,'+@colTotal+' AS Total
            from
            (
                select AdmissionNo,
                    col = Subject+''_''+col,
                    value
                from tblSetMarks t
                cross apply
                (
                    select ''Mark'', Mark union all
                    select ''Total'', Total
                ) c (col, value)
            ) x
            pivot
            (
                SUM(value)
                for col in (' + @cols + ')
            ) p 
			GROUP BY AdmissionNo,' + @cols + ''
 
execute sp_executesql @query;