SQL query to add grand total in Pivot result in SQL Server

Last Reply 3 months ago By dharmendr

Posted 3 months ago

My Table

AdmissionNo Month Total
R-001 1-Jun 500
R-001 2-Feb 500
R-001 3-Mar 500
R-001 4-Apr 500
R-001 5-May 560
R-001 6-Jun 500
R-001 7-Jul 500
R-001 8-Aug 500
R-001 9-Sep 500
R-001 10-Oct 500
R-001 11-Nov 500
R-001 12-Dec 500

required output

AdmissionNo 1-Jun 2-Feb 3-Mar 4-Apr 5-May 6-Jun 7-July 8-Aug 9-Sep 10-Oct 11-Nov 12-Dec Total
R-001 500 500 500 500 500 500 500 500 500 500 500 500 6000

 

                    DECLARE @cols AS NVARCHAR(MAX),
                    @query  AS NVARCHAR(MAX)
                    select @cols = STUFF((SELECT distinct ',' + QUOTENAME(FeesMonth) from tblFeesCollection FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'')

                    set @query = 'SELECT AdmissionNo,SName,FName,AdmissionDate,AcademicName,ClassName,SectionName,' + @cols + ' from 
                                 (
                                    select fc.AdmissionNo,SName,FName,AdmissionDate,AcademicName,ClassName,SectionName,[FeesMonth], Paid
                                    from tblFeesCollection as fc inner join tblStdReg as sr on fc.AdmissionNo=sr.AdmissionNo and Active_Status=''Active'' inner join tblAcademicYear as ay on sr.YearID=ay.YearID inner join tblDefClass as dc on sr.ClassID=dc.ClassID inner join tblDefSection as dsb on sr.SectionID=dsb.SectionID
                                ) x
                                pivot 
                                (
                                    sum(Paid)
                                    for FeesMonth in (' + @cols + ')
                                ) p '

                    execute(@query)

it is not showing data in monthly order and not showing Total

You are viewing reply posted by: dharmendr 3 months ago.
Posted 3 months ago

Hi smile,

In order to show data in monthly order you need to add 0 before the value from 1 to 9.

Check the below test Query.

SQL

CREATE TABLE tblFeesCollection (AdmissionNo VARCHAR(10),FeesMonth VARCHAR(10),Total INT)
INSERT INTO tblFeesCollection VALUES('R-001','01-Jun',500)
INSERT INTO tblFeesCollection VALUES('R-001','02-Feb',500)
INSERT INTO tblFeesCollection VALUES('R-001','03-Mar',500)
INSERT INTO tblFeesCollection VALUES('R-001','04-Apr',500)
INSERT INTO tblFeesCollection VALUES('R-001','05-May',500)
INSERT INTO tblFeesCollection VALUES('R-001','06-Jun',500)
INSERT INTO tblFeesCollection VALUES('R-001','07-Jul',500)
INSERT INTO tblFeesCollection VALUES('R-001','08-Aug',500)
INSERT INTO tblFeesCollection VALUES('R-001','09-Sep',500)
INSERT INTO tblFeesCollection VALUES('R-001','10-Oct',500)
INSERT INTO tblFeesCollection VALUES('R-001','11-Nov',500)
INSERT INTO tblFeesCollection VALUES('R-001','12-Dec',500)

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(FeesMonth) from tblFeesCollection FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'')
 
set @query = 'SELECT AdmissionNo,' + @cols + ',(SELECT SUM(Total) FROM tblFeesCollection) ToTal from
                (
                select *
                from tblFeesCollection
            ) x
            pivot
            (
                sum(Total)
                for FeesMonth in (' + @cols + ')
            ) p ' 
execute(@query)

Output

AdmissionNo

1-Jun

2-Feb

3-Mar

4-Apr

5-May

6-Jun

7-July

8-Aug

9-Sep

10-Oct

11-Nov

12-Dec

Total

R-001

500

500

500

500

500

500

500

500

500

500

500

500

6000