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

Last Reply 5 months ago By pandeyism

Posted 5 months ago
Sl.No Amount CreatedDate StageName Opportunity_Partner
1 40 2019-01-01 Loss Manish
2 35 2019-01-02 Proposal Akash
3 80 2019-01-03 Proposal Ajay
4 250 2019-01-04 Proposal Ajay
5 120 2019-01-05 Loss Vikas
6 234 2019-01-06 Auto Closure Akash
7 32 2019-01-07 Auto Closure Akash
8 34 2019-01-08 Auto Closure Akash
9 4 2019-01-09 Loss Chirag
10 5 2019-01-10 Win Monika
11 32 2019-01-11 Win Vaibhav
12 45 2019-01-12 Win Arti
CREATE TABLE [dbo].[Opportunity_Data](
[SlNo] [int] IDENTITY(1,1) NOT NULL,
[Amount] [float] NULL,
[CreatedDate] [date] NULL,
[StageName] [nvarchar](1000) NULL,
[Opportunity_Partner] [nvarchar](1000) NULL,
CONSTRAINT [PK_Opportunity_Data] PRIMARY KEY CLUSTERED
(
[SlNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

unable to devide value from same table on different conditions 

select Sum(Amount) /(select Sum(Amount) from Opportunity_Data a where a.StageName='Qualified' and a.createddate > '05/31/2018' group by a.Opportunity_Partner) 
from Opportunity_Data O 
where O.StageName='Closed - Sold' 
and O.createddate > '05/31/2018' 
group by O.Opportunity_Partner

Output

Partner Name Win Ratio (%) Loss Ratio (%) Auto closure Ratio (%)
A 32 23 232
B 45 32 12
C 23 43 23

 

Posted 5 months ago

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

Opportunity_Partner Auto Closure Loss Proposal Win
Vikas 32 120 80 32
Manish 234 40 35 5
Chirag 34 4 250 45