In this post we would try to understand PIVOT relational operator in SQL. PIVOT is useful when we need to develop SQL based reports in SSRS. Please note that this post is based on a sample report in CRM 2011 called Competitor Win / Loss and its not a silver bullet. If you are an expert in SQL please ignore this post.
With reference from Microsoft,
"You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output"
Scenario:
The report is based on a very simple concept. An Opportunity which has Competitor / Competitors. As you all know in CRM we might add competitors related to the Opportunity.
We would like to see a report which displays counts of open, won and lost opportunities against corresponding competitor ( if competitor exists ).
This could be achieved in many ways. But here we are going to try it with SQL PIVOT concept.
1. Lets have a look at the sample opportunities in CRM.
2. An opportunity could have more than one competitors.
3. This report uses temporary tables to achieve the expected results. To have a better understanding, lets divide the query into 2 parts. PIVOT would be applied in the second part.
Please note the comments with the first part query.
--Temporary
Table
Create table #CompetitorOppIds (
opportunityid uniqueidentifier,
competitorid uniqueidentifier,
name nvarchar(max),
statecode int,
lostto int
primary key clustered
(
[opportunityid],
[competitorid]
)
)
-- To retrieve
the OPEN Opportunities details
insert into #CompetitorOppIds
select oppcomp.opportunityid,
oppcomp.competitorid,
FilteredCompetitor.name,
o.statecode, 0
from FilteredOpportunity as o
join
FilteredOpportunityCompetitors as oppcomp
on (o.opportunityid =
oppcomp.opportunityid)
-- To retrieve
the Competitor Name
INNER JOIN FilteredCompetitor ON
oppcomp.competitorid =
FilteredCompetitor.competitorid
where o.statecode <> 2
-- To retieve
the Closed Opportunities details ( WON and LOST Opportunities are here)
insert into #CompetitorOppIds
select oppc.opportunityid, oppc.competitorid,
FilteredCompetitor.name,
o.statecode, 0
from FilteredOpportunity as o
join
FilteredOpportunityClose as oppc
on (o.opportunityid = oppc.opportunityid and o.statecode = 2 and oppc.statecode = 1 and oppc.competitorid IS NOT NULL)
-- To retrieve
the Competitor Name
INNER JOIN FilteredCompetitor ON
oppc.competitorid
= FilteredCompetitor.competitorid
--Lets see whats
inside the temp table now
SELECT * FROM
#CompetitorOppIds
4. And we get the following result in the temporary table.
5. Time for PIVOT. We need a count of Opportunities ( Aggregate function ) based on unique values of statecode.
ie: If statecode=1 It means its a WON Opportunity
If statecode=0 It means its a OPEN Opportunity
If statecode=2 It means its a LOST Opportunity
In simple words, we are going to turn the table by setting the unique values as Columns.
Please note the comments with the PIVOT
-- Here we select
the columns to be displayed on our PIVOT result table.
SELECT name,competitorid, [0] AS openopp,
[1] AS
wonopp, [2] AS
lostopp
FROM
--Here we select
the necessary data for the columns we selected above
( SELECT
competitorid,name, statecode
FROM
#CompetitorOppIds ) AS
PivotData
PIVOT
(
-- In this
scenario, we need the COUNT aggregate function
COUNT(statecode)
FOR statecode IN ([0],[1],[2])
) AS PivotResult ORDER BY competitorid
-- finally drop the temporary table
DROP TABLE #CompetitorOppIds
6. Here is the PIVOT results.
7. Here is the whole query
--Temporary
Table
Create table #CompetitorOppIds (
opportunityid uniqueidentifier,
competitorid uniqueidentifier,
name nvarchar(max),
statecode int,
lostto int
primary key clustered
(
[opportunityid],
[competitorid]
)
)
-- To retrieve
the OPEN Opportunities details
insert into #CompetitorOppIds
select oppcomp.opportunityid,
oppcomp.competitorid,
FilteredCompetitor.name,
o.statecode, 0
from FilteredOpportunity as o
join
FilteredOpportunityCompetitors as oppcomp
on (o.opportunityid =
oppcomp.opportunityid)
-- To retrieve
the Competitor Name
INNER JOIN FilteredCompetitor ON
oppcomp.competitorid =
FilteredCompetitor.competitorid
where o.statecode <> 2
-- To retieve
the Closed Opportunities details ( WON and LOST Opportunities are here)
insert into #CompetitorOppIds
select oppc.opportunityid, oppc.competitorid,
FilteredCompetitor.name,
o.statecode, 0
from FilteredOpportunity as o
join
FilteredOpportunityClose as oppc
on (o.opportunityid = oppc.opportunityid and o.statecode = 2 and oppc.statecode = 1 and oppc.competitorid IS NOT NULL)
-- To retrieve
the Competitor Name
INNER JOIN FilteredCompetitor ON
oppc.competitorid
= FilteredCompetitor.competitorid
-- Here we selet
the columns to be displayed on our PIVOT result table.
SELECT name,competitorid, [0] AS openopp,
[1] AS
wonopp, [2] AS
lostopp
FROM
--Here we select
the necessary data for the columns we selected above
( SELECT
competitorid,name, statecode
FROM
#CompetitorOppIds ) AS
PivotData
PIVOT
(
-- In this
scenario, we need the COUNT aggregate function
COUNT(statecode)
FOR statecode IN ([0],[1],[2])
) AS PivotResult ORDER BY competitorid
DROP TABLE #CompetitorOppIds
We need to place the above query in a Dataset and then do the report design
Please refer the below links for the basics.
9. The simple report design is shown is below.
10. Proof is in the pudding !
