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
|
