Help with SQL pivot
Lets say I have a basic query like so:
select t2.INDUSTRY,t2.CountType,t
In this part of the query I would like to deivide the total revenue by the # of Clients...and format to 2 decimal...for currency.
In doing so I need to piviot this data like so.
There would be the following columns in the query:
INdustry
Total Industry Revenue # Clients Average Revenue Per Client - Previous
Total Industry Revenue # Clients Average Revenue Per Client - Current
Total Industry Revenue # Clients Average Revenue Per Client - Next
This should all be in one row.
In the sample data you can see how I need this pivoted. There would not always be a column for Previous / Current / or Next but we should always put something in that column even if its zero.
Architect, Design & Engineering Current 106 1702061.32
Architect, Design & Engineering Previous 85 589036.92
Assisted Living Current 7 155020.00
Assisted Living Previous 5 70200.00
This shoudl be like follows:
Industry/ #Clients/ Previous Revenue/ # Clients /Current Revenue/ # Clients/ Next Revenue
Architect, Design & Engineering 106 1702061.32 85 5l89036.92
select t2.INDUSTRY,t2.CountType,t
In this part of the query I would like to deivide the total revenue by the # of Clients...and format to 2 decimal...for currency.
In doing so I need to piviot this data like so.
There would be the following columns in the query:
INdustry
Total Industry Revenue # Clients Average Revenue Per Client - Previous
Total Industry Revenue # Clients Average Revenue Per Client - Current
Total Industry Revenue # Clients Average Revenue Per Client - Next
This should all be in one row.
In the sample data you can see how I need this pivoted. There would not always be a column for Previous / Current / or Next but we should always put something in that column even if its zero.
Architect, Design & Engineering Current 106 1702061.32
Architect, Design & Engineering Previous 85 589036.92
Assisted Living Current 7 155020.00
Assisted Living Previous 5 70200.00
This shoudl be like follows:
Industry/ #Clients/ Previous Revenue/ # Clients /Current Revenue/ # Clients/ Next Revenue
Architect, Design & Engineering 106 1702061.32 85 5l89036.92
Megan Brooks
Well, that is quite some pivot. It might even be a triple pivot; I'm not sure yet. I don't do these very often so I have to go think about it (which means go play with the query). Fortunately, I do pivots for a hobby...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am putting the original query I used to get the values...I needed to pivot...perhaps you can look at this and help me put this into this logic....this just took an entire extra level of confusing.
Here is my original SQL that gets be the totals. Perhaps this should be refactored to make it easier to manage the pivots/cte.
select t2.INDUSTRY,t2.CountType,t
from
(select t1.INDUSTRY, CountType, count(t1.[Client Name]) as [# Clients], sum(t1.Revenue) as TotalRevenue
from
(SELECT
c.cIndustry as INDUSTRY,
c.cName as [Client Name],
c.cFEIN as [Client ID],
cUdf7 as [ENGAGEMENT PARTNER],
cf.ic_nCATotalYear0 as [Revenue],
'Current' as CountType
FROM cadoc_system.dbo.tSite s
INNER JOIN cadoc_system.dbo.tSiteXCrm
ON s.nid = sc.nidSite
INNER JOIN cadoc_crm.dbo.tClient c
ON sc.nIdClient = c.nid
INNER JOIN cadoc_crm..tClientCustomFi
on c.nid = cf.Clientnid
and c.cSiteCode = 'ROOT'
and c.lActive = 1
where cf.ic_nCATotalYear0 > 0
Union
SELECT
c.cIndustry as INDUSTRY,
c.cName as [Client Name],
c.cFEIN as [Client ID],
cUdf7 as [ENGAGEMENT PARTNER],
cf.ic_nCATotalYear1 as [Revenue],
'Previous' as CountType
FROM cadoc_system.dbo.tSite s
INNER JOIN cadoc_system.dbo.tSiteXCrm
ON s.nid = sc.nidSite
INNER JOIN cadoc_crm.dbo.tClient c
ON sc.nIdClient = c.nid
INNER JOIN cadoc_crm..tClientCustomFi
on c.nid = cf.Clientnid
and c.cSiteCode = 'ROOT'
and c.lActive = 1
where cf.ic_nCATotalYear1 > 0
Union
SELECT
c.cIndustry as INDUSTRY,
c.cName as [Client Name],
c.cFEIN as [Client ID],
cUdf7 as [ENGAGEMENT PARTNER],
cf.ic_nCATotalYear2 as [Revenue],
'Next' as CountType
FROM cadoc_system.dbo.tSite s
INNER JOIN cadoc_system.dbo.tSiteXCrm
ON s.nid = sc.nidSite
INNER JOIN cadoc_crm.dbo.tClient c
ON sc.nIdClient = c.nid
INNER JOIN cadoc_crm..tClientCustomFi
on c.nid = cf.Clientnid
and c.cSiteCode = 'ROOT'
and c.lActive = 1
where cf.ic_nCATotalYear2 > 0)t1
group by t1.INDUSTRY, t1.CountType)t2
order by INDUSTRY
Here is my original SQL that gets be the totals. Perhaps this should be refactored to make it easier to manage the pivots/cte.
select t2.INDUSTRY,t2.CountType,t
from
(select t1.INDUSTRY, CountType, count(t1.[Client Name]) as [# Clients], sum(t1.Revenue) as TotalRevenue
from
(SELECT
c.cIndustry as INDUSTRY,
c.cName as [Client Name],
c.cFEIN as [Client ID],
cUdf7 as [ENGAGEMENT PARTNER],
cf.ic_nCATotalYear0 as [Revenue],
'Current' as CountType
FROM cadoc_system.dbo.tSite s
INNER JOIN cadoc_system.dbo.tSiteXCrm
ON s.nid = sc.nidSite
INNER JOIN cadoc_crm.dbo.tClient c
ON sc.nIdClient = c.nid
INNER JOIN cadoc_crm..tClientCustomFi
on c.nid = cf.Clientnid
and c.cSiteCode = 'ROOT'
and c.lActive = 1
where cf.ic_nCATotalYear0 > 0
Union
SELECT
c.cIndustry as INDUSTRY,
c.cName as [Client Name],
c.cFEIN as [Client ID],
cUdf7 as [ENGAGEMENT PARTNER],
cf.ic_nCATotalYear1 as [Revenue],
'Previous' as CountType
FROM cadoc_system.dbo.tSite s
INNER JOIN cadoc_system.dbo.tSiteXCrm
ON s.nid = sc.nidSite
INNER JOIN cadoc_crm.dbo.tClient c
ON sc.nIdClient = c.nid
INNER JOIN cadoc_crm..tClientCustomFi
on c.nid = cf.Clientnid
and c.cSiteCode = 'ROOT'
and c.lActive = 1
where cf.ic_nCATotalYear1 > 0
Union
SELECT
c.cIndustry as INDUSTRY,
c.cName as [Client Name],
c.cFEIN as [Client ID],
cUdf7 as [ENGAGEMENT PARTNER],
cf.ic_nCATotalYear2 as [Revenue],
'Next' as CountType
FROM cadoc_system.dbo.tSite s
INNER JOIN cadoc_system.dbo.tSiteXCrm
ON s.nid = sc.nidSite
INNER JOIN cadoc_crm.dbo.tClient c
ON sc.nIdClient = c.nid
INNER JOIN cadoc_crm..tClientCustomFi
on c.nid = cf.Clientnid
and c.cSiteCode = 'ROOT'
and c.lActive = 1
where cf.ic_nCATotalYear2 > 0)t1
group by t1.INDUSTRY, t1.CountType)t2
order by INDUSTRY
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much for all the help here!!!