Pivot column to row based on values in another column in SQL Server

Hi iammann,

Refer below sample query.

SQL

CREATE TABLE #TableTest (SlNo INT, Amount INT, CreatedDate DATETIME, StageName VARCHAR(20), Opportunity_Partner VARCHAR(20))
INSERT INTO #TableTest VALUES(1,40,'2019-01-01','Loss','Manish')
INSERT INTO #TableTest VALUES(2,35,'2019-01-02','Proposal','Manish')
INSERT INTO #TableTest VALUES(3,80,'2019-01-03','Proposal','Vikas')
INSERT INTO #TableTest VALUES(4,250,'2019-01-04','Proposal','Chirag')
INSERT INTO #TableTest VALUES(5,120,'2019-01-05','Loss','Vikas')
INSERT INTO #TableTest VALUES(6,234,'2019-01-06','Auto Closure','Manish')
INSERT INTO #TableTest VALUES(7,32,'2019-01-07','Auto Closure','Vikas')
INSERT INTO #TableTest VALUES(8,34,'2019-01-08','Auto Closure','Chirag')
INSERT INTO #TableTest VALUES(9,4,'2019-01-09','Loss','Chirag')
INSERT INTO #TableTest VALUES(10,5,'2019-01-10','Win','Manish')
INSERT INTO #TableTest VALUES(11,32,'2019-01-11','Win','Vikas')
INSERT INTO #TableTest VALUES(12,45,'2019-01-12','Win','Chirag')

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName = ISNULL(@ColumnName + ',','')+ QUOTENAME(StageName) FROM (SELECT DISTINCT [StageName] FROM #TableTest) AS StageName

SET @DynamicPivotQuery = ';WITH CTE AS(SELECT StageName,Opportunity_Partner,Amount FROM #TableTest)
SELECT Opportunity_Partner,'+@ColumnName+' FROM CTE
PIVOT (MAX(Amount) FOR StageName IN('+@ColumnName+')) p
ORDER BY Opportunity_Partner DESC'
EXEC(@DynamicPivotQuery)

DROP TABLE #TableTest

Output