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.Convert Rows To Columns and Calculate Sum using dynamic Pivot query in SQL Server<p>Hi <a class="username" rel="akhter"> akhter</a>,</p> <p>Refer below sample query.</p> <p><span style="text-decoration: underline;"><strong>SQL</strong></span></p> <pre class="brush: sql">CREATE TABLE #tbl1(ItemCode INT, Name VARCHAR(20)) INSERT INTO #tbl1 VALUES(1,&#39;A&#39;) INSERT INTO #tbl1 VALUES(2,&#39;B&#39;) INSERT INTO #tbl1 VALUES(3,&#39;C&#39;) INSERT INTO #tbl1 VALUES(4,&#39;D&#39;) CREATE TABLE #tbl2 (ItemCode INT, Date DATETIME,Itemqty INT) INSERT INTO #tbl2 VALUES(1,&#39;2019-05-01&#39;,10) INSERT INTO #tbl2 VALUES(1,&#39;2019-05-01&#39;,20) INSERT INTO #tbl2 VALUES(1,&#39;2019-05-01&#39;,30) INSERT INTO #tbl2 VALUES(1,&#39;2019-05-01&#39;,40) &nbsp; DECLARE @cols AS NVARCHAR(MAX) DECLARE @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT &#39;,&#39; + QUOTENAME(Date) from #tbl2&nbsp; as t1 inner join #tbl1 as t2 on t1.ItemCode=t2.ItemCode --WHERE Date BETWEEN &#39;2019-05-01&#39; AND &#39;2019-05-03&#39; GROUP BY Date ORDER BY Date FOR XML PATH(&#39;&#39;), TYPE ).value(&#39;.&#39;, &#39;NVARCHAR(MAX)&#39;),1,1,&#39;&#39;) set @query = &#39;;WITH CTE AS( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select tda.Itemqty,tda.Date,dts.Name, dts.ItemCode &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from #tbl1 as dts &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; inner join #tbl2 tda on tda.ItemCode = dts.ItemCode &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE Date BETWEEN &#39;&#39;2019-05-01&#39;&#39; AND &#39;&#39;2019-05-03&#39;&#39; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT SUM(Itemqty) Itemqty,Name,&#39; + @cols + &#39; from CTE&nbsp; &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; pivot(max(ItemCode) for Date in (&#39; + @cols + &#39;)) p &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; GROUP BY Name,&#39; + @cols + &#39; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &#39;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; execute(@query); DROP TABLE #tbl1 DROP TABLE #tbl2</pre> <p>&nbsp; </p>https://www.aspforums.net:443/Threads/214520/Convert-Rows-To-Columns-and-Calculate-Sum-using-dynamic-Pivot-query-in-SQL-Server/https://www.aspforums.net:443/Threads/214520/Convert-Rows-To-Columns-and-Calculate-Sum-using-dynamic-Pivot-query-in-SQL-Server/Fri, 17 May 2019 01:12:30 GMT