[AccessD] Crosstab in Oracle

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>


More information about the AccessD mailing list