Porter, Mark
MPorter at acsalaska.com
Thu May 29 12:27:54 CDT 2003
Below is a SQL Server example. Very rough, could be more elegant I'm sure,
and you may have to do some substitution with Oracle commands, but it may
help you structure it out.
Aside from some extra columns, this report gives categorical sales
breakdowns (sd.SalesCategory) per sales ID, grouped by year and month, with
a totals column.
Select sd.Year as Year
,sd.Month as Month
,Case sd.SalesCategory WHEN 'Non-Strategic' then 'Non-Strategic'
WHEN 'Customer Svc' then 'Customer Svc'
WHEN 'GSR' then 'GSR'
WHEN 'Interconnect' then 'Interconnect'
WHEN 'Internal' then 'Internal'
WHEN 'Strategic Sales' then 'Strategic Sales'
WHEN 'Strategic SOA' then 'Strategic SOA'
WHEN 'Unassigned' then 'Unassigned'
ELSE 'Missing Sales Id' END as SalesCategory
,Case sd.SalesCategory WHEN 'Non-Strategic' then sd.TGTSalesID
WHEN 'Customer Svc' then sd.TGTSalesID
WHEN 'GSR' then sd.TGTSalesID
WHEN 'Interconnect' then sd.TGTSalesID
WHEN 'Internal' then sd.TGTSalesID
WHEN 'Strategic Sales' then sd.TGTSalesID
WHEN 'Strategic SOA' then sd.TGTSalesID
WHEN 'Unassigned' then sd.TGTSalesID
ELSE 'Missing Sales Id' END as TGTSalesID
,Case sd.SalesCategory WHEN 'Non-Strategic' then sd.SalesRep
WHEN 'Customer Svc' then sd.SalesRep
WHEN 'GSR' then sd.SalesRep
WHEN 'Interconnect' then sd.SalesRep
WHEN 'Internal' then sd.SalesRep
WHEN 'Strategic Sales' then sd.SalesRep
WHEN 'Strategic SOA' then sd.SalesRep
WHEN 'Unassigned' then sd.SalesRep
ELSE 'Missing Sales Id' END as SalesRep
,Case sd.SalesCategory WHEN 'Non-Strategic' then sd.SalesID
WHEN 'Customer Svc' then sd.SalesID
WHEN 'GSR' then sd.SalesID
WHEN 'Interconnect' then sd.SalesID
WHEN 'Internal' then sd.SalesID
WHEN 'Strategic Sales' then sd.SalesID
WHEN 'Strategic SOA' then sd.SalesID
WHEN 'Unassigned' then sd.SalesID
ELSE 'Missing Sales Id' END as SalesID
,sum(Case sd.ProductLine WHEN 'InfoServices' then 0
WHEN 'CPE' then 0 ELSE sd.total END) as TargetSales
,sum(sd.Total) as TotalSales
,sum(Case sd.ProductLine WHEN 'Access Revenue' THEN sd.Total ELSE 0 END)
as 'AccessRevenue'
,sum(Case sd.ProductLine WHEN 'Centrex' THEN sd.Total ELSE 0 END) as
'Centrex'
,sum(Case sd.ProductLine WHEN 'CPE' THEN sd.Total ELSE 0 END) as 'CPE'
,sum(Case sd.ProductLine WHEN 'InfoServices' THEN sd.Total ELSE 0 END)
as 'InfoServices'
,sum(Case sd.ProductLine WHEN 'Internet' THEN sd.Total ELSE 0 END) as
'Internet'
,sum(Case sd.ProductLine WHEN 'LecDirectory' THEN sd.Total ELSE 0 END)
as 'LecDirectory'
,sum(Case sd.ProductLine WHEN 'LecInternet' THEN sd.Total ELSE 0 END) as
'LecInternet'
,sum(Case sd.ProductLine WHEN 'Long Distance' THEN sd.Total ELSE 0 END)
as 'LongDistance'
,sum(Case sd.ProductLine WHEN 'Messaging' THEN sd.Total ELSE 0 END) as
'Messaging'
,sum(Case sd.ProductLine WHEN 'Network' THEN sd.Total ELSE 0 END) as
'Network'
,sum(Case sd.ProductLine WHEN 'Switched' THEN sd.Total ELSE 0 END) as
'Switched'
,sum(Case sd.ProductLine WHEN 'Other' THEN sd.Total ELSE 0 END) as
'Other'
,sum(Case sd.ProductLine WHEN 'Access Revenue' THEN 0
WHEN 'Centrex' THEN 0
WHEN 'CPE' THEN 0
WHEN 'InfoServices' THEN 0
WHEN 'Internet' THEN 0
WHEN 'LecDirectory' THEN 0
WHEN 'LecInternet' THEN 0
WHEN 'Long Distance' THEN 0
WHEN 'Messaging' THEN 0
WHEN 'Network' THEN 0
WHEN 'Switched' THEN 0
WHEN 'Other' THEN 0 else sd.Total END) as 'Undefined'
FROM dbo.tblSalesTargets t RIGHT OUTER JOIN
dbo.vwSalesDetail_Full sd ON
t.TGTSalesID = sd.TGTSalesID
AND
t.Year = sd.Year AND
t.Month = sd.Month
--where sd.year = '2002' and sd.month = 8
Group By sd.Year
,sd.Month
,Case sd.SalesCategory WHEN 'Non-Strategic' then 'Non-Strategic'
WHEN 'Customer Svc' then 'Customer Svc'
WHEN 'GSR' then 'GSR'
WHEN 'Interconnect' then 'Interconnect'
WHEN 'Internal' then 'Internal'
WHEN 'Strategic Sales' then 'Strategic Sales'
WHEN 'Strategic SOA' then 'Strategic SOA'
WHEN 'Unassigned' then 'Unassigned'
ELSE 'Missing Sales Id' END
,Case sd.SalesCategory WHEN 'Non-Strategic' then sd.TGTSalesID
WHEN 'Customer Svc' then sd.TGTSalesID
WHEN 'GSR' then sd.TGTSalesID
WHEN 'Interconnect' then sd.TGTSalesID
WHEN 'Internal' then sd.TGTSalesID
WHEN 'Strategic Sales' then sd.TGTSalesID
WHEN 'Strategic SOA' then sd.TGTSalesID
WHEN 'Unassigned' then sd.TGTSalesID
ELSE 'Missing Sales Id' END
,Case sd.SalesCategory WHEN 'Non-Strategic' then sd.SalesRep
WHEN 'Customer Svc' then sd.SalesRep
WHEN 'GSR' then sd.SalesRep
WHEN 'Interconnect' then sd.SalesRep
WHEN 'Internal' then sd.SalesRep
WHEN 'Strategic Sales' then sd.SalesRep
WHEN 'Strategic SOA' then sd.SalesRep
WHEN 'Unassigned' then sd.SalesRep
ELSE 'Missing Sales Id' END
,Case sd.SalesCategory WHEN 'Non-Strategic' then sd.SalesID
WHEN 'Customer Svc' then sd.SalesID
WHEN 'GSR' then sd.SalesID
WHEN 'Interconnect' then sd.SalesID
WHEN 'Internal' then sd.SalesID
WHEN 'Strategic Sales' then sd.SalesID
WHEN 'Strategic SOA' then sd.SalesID
WHEN 'Unassigned' then sd.SalesID
ELSE 'Missing Sales Id' END
-- I've pulled out the lines for the summary columns per product line. The
rest group the data to be summarized.
,sum(Case sd.ProductLine WHEN 'InfoServices' then 0
WHEN 'CPE' then 0 ELSE sd.total END) as TargetSales
,sum(sd.Total) as TotalSales
,sum(Case sd.ProductLine WHEN 'Access Revenue' THEN sd.Total ELSE 0 END)
as 'AccessRevenue'
,sum(Case sd.ProductLine WHEN 'Centrex' THEN sd.Total ELSE 0 END) as
'Centrex'
,sum(Case sd.ProductLine WHEN 'CPE' THEN sd.Total ELSE 0 END) as 'CPE'
,sum(Case sd.ProductLine WHEN 'InfoServices' THEN sd.Total ELSE 0 END)
as 'InfoServices'
,sum(Case sd.ProductLine WHEN 'Internet' THEN sd.Total ELSE 0 END) as
'Internet'
,sum(Case sd.ProductLine WHEN 'LecDirectory' THEN sd.Total ELSE 0 END)
as 'LecDirectory'
,sum(Case sd.ProductLine WHEN 'LecInternet' THEN sd.Total ELSE 0 END) as
'LecInternet'
,sum(Case sd.ProductLine WHEN 'Long Distance' THEN sd.Total ELSE 0 END)
as 'LongDistance'
,sum(Case sd.ProductLine WHEN 'Messaging' THEN sd.Total ELSE 0 END) as
'Messaging'
,sum(Case sd.ProductLine WHEN 'Network' THEN sd.Total ELSE 0 END) as
'Network'
,sum(Case sd.ProductLine WHEN 'Switched' THEN sd.Total ELSE 0 END) as
'Switched'
,sum(Case sd.ProductLine WHEN 'Other' THEN sd.Total ELSE 0 END) as
'Other'
,sum(Case sd.ProductLine WHEN 'Access Revenue' THEN 0
WHEN 'Centrex' THEN 0
WHEN 'CPE' THEN 0
WHEN 'InfoServices' THEN 0
WHEN 'Internet' THEN 0
WHEN 'LecDirectory' THEN 0
WHEN 'LecInternet' THEN 0
WHEN 'Long Distance' THEN 0
WHEN 'Messaging' THEN 0
WHEN 'Network' THEN 0
WHEN 'Switched' THEN 0
WHEN 'Other' THEN 0 else sd.Total END) as 'Undefined'
--
The rest are group by columns. Hope this helps.
Mark
-----Original Message-----
From: Anita Smith [mailto:anita at ddisolutions.com.au]
Sent: Wednesday, May 28, 2003 4:35 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Crosstab in Oracle
Hi Guys,
Does anyone on this great list have any suggestions on how to achieve the
equivalent of a crosstab query in Oracle SQL.
Any suggestions would be greatly appreciated.
Anita Smith
Australia
This transmittal may contain confidential information intended solely for
the addressee. If you are not the intended recipient, you are hereby
notified that you have received this transmittal in error; any review,
dissemination, distribution or copying of this transmittal is strictly
prohibited. If you have received this communication in error, please notify
us immediately by reply or by telephone (collect at 907-564-1000) and ask to
speak with the message sender. In addition, please immediately delete this
message and all attachments. Thank you.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030529/3d31c8d8/attachment-0001.html>