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.Pivot column to row based on values in another column in SQL Server<p>Hi <a class="username" rel="iammann"> iammann</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 #TableTest (SlNo INT, Amount INT, CreatedDate DATETIME, StageName VARCHAR(20), Opportunity_Partner VARCHAR(20)) INSERT INTO #TableTest VALUES(1,40,&#39;2019-01-01&#39;,&#39;Loss&#39;,&#39;Manish&#39;) INSERT INTO #TableTest VALUES(2,35,&#39;2019-01-02&#39;,&#39;Proposal&#39;,&#39;Manish&#39;) INSERT INTO #TableTest VALUES(3,80,&#39;2019-01-03&#39;,&#39;Proposal&#39;,&#39;Vikas&#39;) INSERT INTO #TableTest VALUES(4,250,&#39;2019-01-04&#39;,&#39;Proposal&#39;,&#39;Chirag&#39;) INSERT INTO #TableTest VALUES(5,120,&#39;2019-01-05&#39;,&#39;Loss&#39;,&#39;Vikas&#39;) INSERT INTO #TableTest VALUES(6,234,&#39;2019-01-06&#39;,&#39;Auto Closure&#39;,&#39;Manish&#39;) INSERT INTO #TableTest VALUES(7,32,&#39;2019-01-07&#39;,&#39;Auto Closure&#39;,&#39;Vikas&#39;) INSERT INTO #TableTest VALUES(8,34,&#39;2019-01-08&#39;,&#39;Auto Closure&#39;,&#39;Chirag&#39;) INSERT INTO #TableTest VALUES(9,4,&#39;2019-01-09&#39;,&#39;Loss&#39;,&#39;Chirag&#39;) INSERT INTO #TableTest VALUES(10,5,&#39;2019-01-10&#39;,&#39;Win&#39;,&#39;Manish&#39;) INSERT INTO #TableTest VALUES(11,32,&#39;2019-01-11&#39;,&#39;Win&#39;,&#39;Vikas&#39;) INSERT INTO #TableTest VALUES(12,45,&#39;2019-01-12&#39;,&#39;Win&#39;,&#39;Chirag&#39;) DECLARE @DynamicPivotQuery AS NVARCHAR(MAX) DECLARE @ColumnName AS NVARCHAR(MAX) SELECT @ColumnName = ISNULL(@ColumnName + &#39;,&#39;,&#39;&#39;)+ QUOTENAME(StageName) FROM (SELECT DISTINCT [StageName] FROM #TableTest) AS StageName SET @DynamicPivotQuery = &#39;;WITH CTE AS(SELECT StageName,Opportunity_Partner,Amount FROM #TableTest) SELECT Opportunity_Partner,&#39;+@ColumnName+&#39; FROM CTE PIVOT (MAX(Amount) FOR StageName IN(&#39;+@ColumnName+&#39;)) p ORDER BY Opportunity_Partner DESC&#39; EXEC(@DynamicPivotQuery) DROP TABLE #TableTest</pre> <p><span style="text-decoration: underline;"><strong>Output</strong></span></p> <table border="1"> <tbody> <tr style="height: 15.0pt;"> <td style="height: 15.0pt; width: 48pt;" width="64" height="20"><strong>Opportunity_Partner</strong></td> <td style="width: 48pt;" width="64"><strong>Auto Closure</strong></td> <td style="width: 48pt;" width="64"><strong>Loss</strong></td> <td style="width: 48pt;" width="64"><strong>Proposal</strong></td> <td style="width: 48pt;" width="64"><strong>Win</strong></td> </tr> <tr style="height: 15.0pt;"> <td style="height: 15.0pt;" height="20">Vikas</td> <td align="right">32</td> <td align="right">120</td> <td align="right">80</td> <td align="right">32</td> </tr> <tr style="height: 15.0pt;"> <td style="height: 15.0pt;" height="20">Manish</td> <td align="right">234</td> <td align="right">40</td> <td align="right">35</td> <td align="right">5</td> </tr> <tr style="height: 15.0pt;"> <td style="height: 15.0pt;" height="20">Chirag</td> <td align="right">34</td> <td align="right">4</td> <td align="right">250</td> <td align="right">45</td> </tr> </tbody> </table>https://www.aspforums.net:443/Threads/138167/Pivot-column-to-row-based-on-values-in-another-column-in-SQL-Server/https://www.aspforums.net:443/Threads/138167/Pivot-column-to-row-based-on-values-in-another-column-in-SQL-Server/Thu, 13 Jun 2019 05:06:45 GMT