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.Calculate Row Total and Column Total using dynamic Pivot query in SQL Server<p>Hi&nbsp;akhter,</p> <p>Please check the below query and modify your query according to your table structure.</p> <p><strong><span style="text-decoration: underline;">SQL</span></strong></p> <pre class="brush: sql">CREATE TABLE #FOOD(Id INT, NAME VARCHAR(10)) INSERT INTO #FOOD(Id, NAME) SELECT 1,&#39;apple&#39; UNION ALL SELECT 2,&#39;orange&#39; UNION ALL SELECT 3,&#39;tomato&#39; CREATE TABLE #PEOPLE(Id INT, NAME VARCHAR(10)) INSERT INTO #PEOPLE(Id, NAME) SELECT 1,&#39;Imomdod&#39; UNION ALL SELECT 2,&#39;Muhammad&#39; UNION ALL SELECT 3,&#39;Alisher&#39; CREATE TABLE #SALES(Id INT, IdPer INT, IDFOOD INT, price float) INSERT INTO #SALES(Id, IdPer, IDFOOD, price) SELECT 1,2,1, 3 UNION ALL SELECT 2,3,1, 5 UNION ALL SELECT 3,1,3, 11 UNION ALL SELECT 4,3,2, 2 UNION ALL SELECT 5,2,1, 5 UNION ALL SELECT 6,1,2, 8 UNION ALL SELECT 7,2,3, 4 UNION ALL SELECT 8,3,2, 10 UNION ALL SELECT 9,2,3, 15 UNION ALL SELECT 10,1,2, 18 UNION ALL SELECT 11,3,3, 13 UNION ALL SELECT 12,1,3, 19 DECLARE @DynamicPivotQuery AS NVARCHAR(MAX) DECLARE @ColumnName AS NVARCHAR(MAX) SELECT @ColumnName = ISNULL(@ColumnName + &#39;,&#39;,&#39;&#39;)+ QUOTENAME(NAME) FROM (SELECT DISTINCT [NAME] FROM #FOOD) AS [NAME] DECLARE @ColumnForSum AS NVARCHAR(MAX) SELECT @ColumnForSum = REPLACE(@ColumnName,&#39;,&#39;,&#39;+&#39;) SELECT @ColumnForSum = REPLACE(@ColumnForSum,&#39;[&#39;,&#39;ISNULL([&#39;) SELECT @ColumnForSum = REPLACE(@ColumnForSum,&#39;]&#39;,&#39;],0)&#39;) DECLARE @RowForSum AS NVARCHAR(MAX) SELECT @RowForSum = REPLACE(@ColumnName,&#39;,&#39;,&#39;,&#39;) SELECT @RowForSum = REPLACE(@RowForSum,&#39;[&#39;,&#39;SUM(ISNULL([&#39;) SELECT @RowForSum = REPLACE(@RowForSum,&#39;]&#39;,&#39;],0))&#39;) SET @DynamicPivotQuery = &#39;;WITH cte AS ( SELECT * FROM ( SELECT PersonName,&#39;+@ColumnName+&#39; ,SUM(&#39;+@ColumnForSum+&#39;) Summa FROM ( SELECT SUM(s.Price) Price,p.NAME as PersonName,f.NAME as FoodName from #SALES s INNER JOIN #PEOPLE p ON s.IdPer = p.Id INNER JOIN #FOOD f ON s.IDFOOD = f.Id GROUP BY p.Name,f.Name )t pivot ( MAX([Price]) FOR FoodName IN (&#39;+ @ColumnName +&#39;) ) piv GROUP BY PersonName,&#39;+ @ColumnName +&#39; ) r ) SELECT PersonName,&#39;+ @ColumnName +&#39;,Summa FROM cte UNION SELECT &#39;&#39;Total&#39;&#39;,&#39;+ @RowForSum +&#39;,SUM(Summa) FROM cte&#39; EXEC (@DynamicPivotQuery) DROP TABLE #SALES DROP TABLE #FOOD DROP TABLE #PEOPLE</pre> <p><strong><span style="text-decoration: underline;">Output</span></strong></p> <table border="1"> <tbody> <tr align="center"> <td><strong>PersonName</strong></td> <td><strong>apple</strong></td> <td><strong>orange</strong></td> <td><strong>tomato</strong></td> <td><strong>Summa</strong></td> </tr> <tr> <td>Alisher</td> <td>5</td> <td>12</td> <td>13</td> <td>30</td> </tr> <tr> <td>Imomdod</td> <td>NULL</td> <td>26</td> <td>30</td> <td>56</td> </tr> <tr> <td>Muhammad</td> <td>8</td> <td>NULL</td> <td>19</td> <td>27</td> </tr> <tr> <td>Total</td> <td>13</td> <td>38</td> <td>62</td> <td>113</td> </tr> </tbody> </table>https://www.aspforums.net:443/Threads/165889/Calculate-Row-Total-and-Column-Total-using-dynamic-Pivot-query-in-SQL-Server/https://www.aspforums.net:443/Threads/165889/Calculate-Row-Total-and-Column-Total-using-dynamic-Pivot-query-in-SQL-Server/Thu, 20 Jun 2019 07:42:13 GMT