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.Using multiple aggregate function in dynamic PIVOT query in SQL Server<p>Hi<a class="username" rel="vishalkal">vishalkal</a>,</p> <p>Check this below example.</p> <p><strong><span style="text-decoration: underline;">SQL</span></strong></p> <pre class="brush: sql">CREATE TABLE FinalSupplyReconciliation(ItemDescr varchar(5),InvoiceNo varchar(3),PORate int,Amt int) INSERT INTO FinalSupplyReconciliation VALUES(&#39;DD1&#39;,&#39;A&#39;,1000,10) INSERT INTO FinalSupplyReconciliation VALUES(&#39;DD1&#39;,&#39;B&#39;,2000,20) INSERT INTO FinalSupplyReconciliation VALUES(&#39;DD1&#39;,&#39;C&#39;,3000,30) INSERT INTO FinalSupplyReconciliation VALUES(&#39;DD1&#39;,&#39;A&#39;,4000,40) INSERT INTO FinalSupplyReconciliation VALUES(&#39;DD1&#39;,&#39;B&#39;,5000,50) INSERT INTO FinalSupplyReconciliation VALUES(&#39;DD1&#39;,&#39;C&#39;,6000,60) INSERT INTO FinalSupplyReconciliation VALUES(&#39;DD2&#39;,&#39;A&#39;,7000,70) INSERT INTO FinalSupplyReconciliation VALUES(&#39;DD2&#39;,&#39;B&#39;,8000,80) INSERT INTO FinalSupplyReconciliation VALUES(&#39;DD2&#39;,&#39;C&#39;,9000,90) INSERT INTO FinalSupplyReconciliation VALUES(&#39;DD2&#39;,&#39;A&#39;,10000,100) INSERT INTO FinalSupplyReconciliation VALUES(&#39;DD2&#39;,&#39;B&#39;,11000,110) INSERT INTO FinalSupplyReconciliation VALUES(&#39;DD2&#39;,&#39;C&#39;,1200,120) DECLARE @cols AS NVARCHAR(MAX),@query AS NVARCHAR(MAX) select @cols = STUFF((SELECT &#39;,&#39; + QUOTENAME(InvoiceNo+&#39;_&#39;+col) from FinalSupplyReconciliation t cross apply ( select &#39;PORate&#39;, 1 union all select &#39;Amt&#39;, 2 ) c (col, so) group by col, so, InvoiceNo order by InvoiceNo, so FOR XML PATH(&#39;&#39;), TYPE ).value(&#39;.&#39;, &#39;NVARCHAR(MAX)&#39;) ,1,1,&#39;&#39;) set @query = &#39;SELECT ItemDescr,&#39; + @cols + &#39; from ( select ItemDescr, col = InvoiceNo+&#39;&#39;_&#39;&#39;+col, value from FinalSupplyReconciliation t cross apply ( select &#39;&#39;PORate&#39;&#39;, PORate union all select &#39;&#39;Amt&#39;&#39;, Amt ) c (col, value) ) x pivot ( SUM(value) for col in (&#39; + @cols + &#39;) ) p &#39; execute sp_executesql @query;</pre> <p><strong><span style="text-decoration: underline;">Output</span></strong></p> <table border="1"> <tbody> <tr> <td>ItemDescr</td> <td>A_PORate</td> <td>A_Amt</td> <td>B_PORate</td> <td>B_Amt</td> <td>C_PORate</td> <td>C_Amt</td> </tr> <tr> <td>DD1</td> <td>5000</td> <td>50</td> <td>7000</td> <td>70</td> <td>9000</td> <td>90</td> </tr> <tr> <td>DD2</td> <td>17000</td> <td>170</td> <td>19000</td> <td>190</td> <td>10200</td> <td>210</td> </tr> </tbody> </table>https://www.aspforums.net:443/Threads/158260/Using-multiple-aggregate-function-in-dynamic-PIVOT-query-in-SQL-Server/https://www.aspforums.net:443/Threads/158260/Using-multiple-aggregate-function-in-dynamic-PIVOT-query-in-SQL-Server/Fri, 27 Jul 2018 06:59:08 GMT