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

Last Reply 7 months ago By pandeyism

Posted 7 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

 

This question does not have replies that have been liked.