ASPForums.Net RSS Feedhttp://www.aspforums.net/Handlers/RSS.ashxLatest additions to the content that appears on ASPForums.Net(c) 2019 www.aspforums.com. All rights reserved.SQL query to add grand total in Pivot result in SQL Server<p>Hi <a class="username" rel="smile"> smile</a>,</p> <p>In order to show data in monthly order you need to add <strong>0</strong> before the value from 1 to 9.</p> <p>Check the below test Query.</p> <p><strong><span style="text-decoration: underline;">SQL</span></strong></p> <pre class="brush: sql">CREATE TABLE tblFeesCollection (AdmissionNo VARCHAR(10),FeesMonth VARCHAR(10),Total INT) INSERT INTO tblFeesCollection VALUES(&#39;R-001&#39;,&#39;01-Jun&#39;,500) INSERT INTO tblFeesCollection VALUES(&#39;R-001&#39;,&#39;02-Feb&#39;,500) INSERT INTO tblFeesCollection VALUES(&#39;R-001&#39;,&#39;03-Mar&#39;,500) INSERT INTO tblFeesCollection VALUES(&#39;R-001&#39;,&#39;04-Apr&#39;,500) INSERT INTO tblFeesCollection VALUES(&#39;R-001&#39;,&#39;05-May&#39;,500) INSERT INTO tblFeesCollection VALUES(&#39;R-001&#39;,&#39;06-Jun&#39;,500) INSERT INTO tblFeesCollection VALUES(&#39;R-001&#39;,&#39;07-Jul&#39;,500) INSERT INTO tblFeesCollection VALUES(&#39;R-001&#39;,&#39;08-Aug&#39;,500) INSERT INTO tblFeesCollection VALUES(&#39;R-001&#39;,&#39;09-Sep&#39;,500) INSERT INTO tblFeesCollection VALUES(&#39;R-001&#39;,&#39;10-Oct&#39;,500) INSERT INTO tblFeesCollection VALUES(&#39;R-001&#39;,&#39;11-Nov&#39;,500) INSERT INTO tblFeesCollection VALUES(&#39;R-001&#39;,&#39;12-Dec&#39;,500) DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT distinct &#39;,&#39; + QUOTENAME(FeesMonth) from tblFeesCollection FOR XML PATH(&#39;&#39;), TYPE ).value(&#39;.&#39;, &#39;NVARCHAR(MAX)&#39;) ,1,1,&#39;&#39;) set @query = &#39;SELECT AdmissionNo,&#39; + @cols + &#39;,(SELECT SUM(Total) FROM tblFeesCollection) ToTal from ( select * from tblFeesCollection ) x pivot ( sum(Total) for FeesMonth in (&#39; + @cols + &#39;) ) p &#39; execute(@query)</pre> <p><strong><span style="text-decoration: underline;">Output</span></strong></p> <table> <tbody> <tr> <td width="95"> <p><strong>AdmissionNo</strong></p> </td> <td width="62"> <p><strong>1-Jun</strong></p> </td> <td width="62"> <p><strong>2-Feb</strong></p> </td> <td width="63"> <p><strong>3-Mar</strong></p> </td> <td width="62"> <p><strong>4-Apr</strong></p> </td> <td width="64"> <p><strong>5-May</strong></p> </td> <td width="62"> <p><strong>6-Jun</strong></p> </td> <td width="63"> <p><strong>7-July</strong></p> </td> <td width="63"> <p><strong>8-Aug</strong></p> </td> <td width="56"> <p><strong>9-Sep</strong></p> </td> <td width="57"> <p><strong>10-Oct</strong></p> </td> <td width="61"> <p><strong>11-Nov</strong></p> </td> <td width="58"> <p><strong>12-Dec</strong></p> </td> <td width="51"> <p><strong>Total</strong></p> </td> </tr> <tr> <td width="95"> <p><strong>R-001</strong></p> </td> <td width="62"> <p>500</p> </td> <td width="62"> <p>500</p> </td> <td width="63"> <p>500</p> </td> <td width="62"> <p>500</p> </td> <td width="64"> <p>500</p> </td> <td width="62"> <p>500</p> </td> <td width="63"> <p>500</p> </td> <td width="63"> <p>500</p> </td> <td width="56"> <p>500</p> </td> <td width="57"> <p>500</p> </td> <td width="61"> <p>500</p> </td> <td width="58"> <p>500</p> </td> <td width="51"> <p>6000</p> </td> </tr> </tbody> </table>https://www.aspforums.net:443/Threads/133661/SQL-query-to-add-grand-total-in-Pivot-result-in-SQL-Server/https://www.aspforums.net:443/Threads/133661/SQL-query-to-add-grand-total-in-Pivot-result-in-SQL-Server/Tue, 02 Oct 2018 02:17:34 GMT