<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META content="MSHTML 5.50.4923.2500" name=GENERATOR>
<STYLE>@page Section1 {size: 595.3pt 841.9pt; margin: 72.0pt 90.0pt 72.0pt 90.0pt; }
P.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"
}
LI.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"
}
DIV.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"
}
A:link {
COLOR: blue; TEXT-DECORATION: underline
}
SPAN.MsoHyperlink {
COLOR: blue; TEXT-DECORATION: underline
}
A:visited {
COLOR: purple; TEXT-DECORATION: underline
}
SPAN.MsoHyperlinkFollowed {
COLOR: purple; TEXT-DECORATION: underline
}
SPAN.EmailStyle17 {
COLOR: windowtext; FONT-FAMILY: Arial
}
DIV.Section1 {
page: Section1
}
</STYLE>
</HEAD>
<BODY lang=EN-AU vLink=purple link=blue>
<DIV><SPAN class=135150817-29052003><FONT face=Arial color=#0000ff size=2>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.</FONT></SPAN></DIV>
<DIV><SPAN class=135150817-29052003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=135150817-29052003><FONT face=Arial color=#0000ff size=2>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.</FONT></SPAN></DIV>
<DIV><SPAN class=135150817-29052003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=135150817-29052003><FONT face=Arial color=#0000ff
size=2>Select sd.Year as
Year<BR> ,sd.Month as
Month <BR> ,Case
sd.SalesCategory WHEN 'Non-Strategic' then
'Non-Strategic' <BR>
WHEN 'Customer Svc' then 'Customer Svc'<BR> WHEN
'GSR' then 'GSR'<BR> WHEN 'Interconnect' then
'Interconnect'<BR> WHEN 'Internal' then
'Internal'<BR> WHEN 'Strategic Sales' then 'Strategic
Sales'<BR> WHEN 'Strategic SOA' then 'Strategic
SOA'<BR> WHEN 'Unassigned' then
'Unassigned'<BR> ELSE 'Missing Sales Id' END as
SalesCategory<BR> ,Case sd.SalesCategory WHEN 'Non-Strategic' then
sd.TGTSalesID <BR>
WHEN 'Customer Svc' then sd.TGTSalesID<BR> WHEN
'GSR' then sd.TGTSalesID<BR> WHEN 'Interconnect'
then sd.TGTSalesID<BR> WHEN 'Internal' then
sd.TGTSalesID<BR> WHEN 'Strategic Sales' then
sd.TGTSalesID<BR> WHEN 'Strategic SOA' then
sd.TGTSalesID<BR> WHEN 'Unassigned' then
sd.TGTSalesID<BR> ELSE 'Missing Sales Id' END as
TGTSalesID<BR> ,Case sd.SalesCategory WHEN 'Non-Strategic' then
sd.SalesRep <BR>
WHEN 'Customer Svc' then sd.SalesRep<BR> WHEN
'GSR' then sd.SalesRep<BR> WHEN 'Interconnect'
then sd.SalesRep<BR> WHEN 'Internal' then
sd.SalesRep<BR> WHEN 'Strategic Sales' then
sd.SalesRep<BR> WHEN 'Strategic SOA' then
sd.SalesRep<BR> WHEN 'Unassigned' then
sd.SalesRep<BR> ELSE 'Missing Sales Id' END as
SalesRep<BR> ,Case sd.SalesCategory WHEN 'Non-Strategic' then
sd.SalesID<BR> WHEN 'Customer Svc' then
sd.SalesID<BR> WHEN 'GSR' then
sd.SalesID<BR> WHEN 'Interconnect' then
sd.SalesID<BR> WHEN 'Internal' then
sd.SalesID<BR> WHEN 'Strategic Sales' then
sd.SalesID<BR> WHEN 'Strategic SOA' then
sd.SalesID<BR> WHEN 'Unassigned' then
sd.SalesID<BR> ELSE 'Missing Sales Id' END as
SalesID<BR> ,sum(Case sd.ProductLine WHEN 'InfoServices' then
0<BR> WHEN 'CPE' then 0 ELSE sd.total END)
as TargetSales<BR> ,sum(sd.Total) as TotalSales<BR> ,sum(Case
sd.ProductLine WHEN 'Access Revenue' THEN sd.Total ELSE 0 END) as
'AccessRevenue'<BR> ,sum(Case sd.ProductLine WHEN 'Centrex' THEN
sd.Total ELSE 0 END) as 'Centrex'<BR> ,sum(Case sd.ProductLine WHEN
'CPE' THEN sd.Total ELSE 0 END) as 'CPE'<BR> ,sum(Case
sd.ProductLine WHEN 'InfoServices' THEN sd.Total ELSE 0 END) as
'InfoServices'<BR> ,sum(Case sd.ProductLine WHEN 'Internet' THEN
sd.Total ELSE 0 END) as 'Internet'<BR> ,sum(Case sd.ProductLine WHEN
'LecDirectory' THEN sd.Total ELSE 0 END) as 'LecDirectory'<BR>
,sum(Case sd.ProductLine WHEN 'LecInternet' THEN sd.Total ELSE 0
END) as 'LecInternet'<BR> ,sum(Case sd.ProductLine WHEN 'Long
Distance' THEN sd.Total ELSE 0 END) as 'LongDistance'<BR>
,sum(Case sd.ProductLine WHEN 'Messaging' THEN sd.Total ELSE 0 END)
as 'Messaging'<BR> ,sum(Case sd.ProductLine WHEN 'Network' THEN
sd.Total ELSE 0 END) as 'Network'<BR> ,sum(Case sd.ProductLine WHEN
'Switched' THEN sd.Total ELSE 0 END) as 'Switched'<BR>
,sum(Case sd.ProductLine WHEN 'Other' THEN sd.Total ELSE 0
END) as 'Other'<BR> ,sum(Case sd.ProductLine WHEN 'Access Revenue'
THEN 0<BR> WHEN 'Centrex' THEN
0<BR> WHEN 'CPE' THEN
0<BR> WHEN 'InfoServices' THEN
0<BR> WHEN 'Internet' THEN
0<BR> WHEN 'LecDirectory' THEN
0<BR> WHEN 'LecInternet' THEN
0<BR> WHEN 'Long Distance' THEN
0<BR> WHEN 'Messaging' THEN
0<BR> WHEN 'Network' THEN
0<BR> WHEN 'Switched' THEN
0<BR> WHEN 'Other' THEN 0 else
sd.Total END) as 'Undefined'<BR>FROM dbo.tblSalesTargets t RIGHT OUTER
JOIN<BR> dbo.vwSalesDetail_Full sd ON<BR>
t.TGTSalesID = sd.TGTSalesID<BR>
AND<BR> t.Year = sd.Year AND<BR> t.Month =
sd.Month<BR>--where sd.year = '2002' and sd.month = 8<BR>Group By
sd.Year<BR> ,sd.Month<BR> ,Case sd.SalesCategory WHEN
'Non-Strategic' then 'Non-Strategic'<BR> WHEN 'Customer
Svc' then 'Customer Svc'<BR> WHEN 'GSR' then
'GSR'<BR> WHEN 'Interconnect' then
'Interconnect'<BR> WHEN 'Internal' then
'Internal'<BR> WHEN 'Strategic Sales' then 'Strategic
Sales'<BR> WHEN 'Strategic SOA' then 'Strategic
SOA'<BR> WHEN 'Unassigned' then
'Unassigned'<BR> ELSE 'Missing Sales Id'
END<BR> ,Case sd.SalesCategory WHEN 'Non-Strategic' then
sd.TGTSalesID<BR> WHEN 'Customer Svc' then
sd.TGTSalesID<BR> WHEN 'GSR' then
sd.TGTSalesID<BR> WHEN 'Interconnect' then
sd.TGTSalesID<BR> WHEN 'Internal' then
sd.TGTSalesID<BR> WHEN 'Strategic Sales' then
sd.TGTSalesID<BR> WHEN 'Strategic SOA' then
sd.TGTSalesID<BR> WHEN 'Unassigned' then
sd.TGTSalesID<BR> ELSE 'Missing Sales Id'
END<BR> ,Case sd.SalesCategory WHEN 'Non-Strategic' then
sd.SalesRep<BR> WHEN 'Customer Svc' then
sd.SalesRep<BR> WHEN 'GSR' then
sd.SalesRep<BR> WHEN 'Interconnect' then
sd.SalesRep<BR> WHEN 'Internal' then
sd.SalesRep<BR> WHEN 'Strategic Sales' then
sd.SalesRep<BR> WHEN 'Strategic SOA' then
sd.SalesRep<BR> WHEN 'Unassigned' then
sd.SalesRep<BR> ELSE 'Missing Sales Id'
END<BR> ,Case sd.SalesCategory WHEN 'Non-Strategic' then
sd.SalesID<BR> WHEN 'Customer Svc' then
sd.SalesID<BR> WHEN 'GSR' then
sd.SalesID<BR> WHEN 'Interconnect' then
sd.SalesID<BR> WHEN 'Internal' then
sd.SalesID<BR> WHEN 'Strategic Sales' then
sd.SalesID<BR> WHEN 'Strategic SOA' then
sd.SalesID<BR> WHEN 'Unassigned' then
sd.SalesID<BR> ELSE 'Missing Sales Id'
END</FONT></SPAN></DIV>
<DIV><SPAN class=135150817-29052003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=135150817-29052003><FONT face=Arial color=#0000ff size=2>--
I've pulled out the lines for the summary columns per product line. The
rest group the data to be summarized.</FONT></SPAN></DIV>
<DIV><SPAN class=135150817-29052003><FONT face=Arial color=#0000ff size=2>
,sum(Case sd.ProductLine WHEN 'InfoServices' then
0<BR> WHEN 'CPE' then 0 ELSE sd.total END)
as TargetSales<BR> ,sum(sd.Total) as TotalSales<BR> ,sum(Case
sd.ProductLine WHEN 'Access Revenue' THEN sd.Total ELSE 0 END) as
'AccessRevenue'<BR> ,sum(Case sd.ProductLine WHEN 'Centrex' THEN
sd.Total ELSE 0 END) as 'Centrex'<BR> ,sum(Case sd.ProductLine WHEN
'CPE' THEN sd.Total ELSE 0 END) as 'CPE'<BR> ,sum(Case
sd.ProductLine WHEN 'InfoServices' THEN sd.Total ELSE 0 END) as
'InfoServices'<BR> ,sum(Case sd.ProductLine WHEN 'Internet' THEN
sd.Total ELSE 0 END) as 'Internet'<BR> ,sum(Case sd.ProductLine WHEN
'LecDirectory' THEN sd.Total ELSE 0 END) as 'LecDirectory'<BR>
,sum(Case sd.ProductLine WHEN 'LecInternet' THEN sd.Total ELSE 0
END) as 'LecInternet'<BR> ,sum(Case sd.ProductLine WHEN 'Long
Distance' THEN sd.Total ELSE 0 END) as 'LongDistance'<BR>
,sum(Case sd.ProductLine WHEN 'Messaging' THEN sd.Total ELSE 0 END)
as 'Messaging'<BR> ,sum(Case sd.ProductLine WHEN 'Network' THEN
sd.Total ELSE 0 END) as 'Network'<BR> ,sum(Case sd.ProductLine WHEN
'Switched' THEN sd.Total ELSE 0 END) as 'Switched'<BR>
,sum(Case sd.ProductLine WHEN 'Other' THEN sd.Total ELSE 0
END) as 'Other'<BR> ,sum(Case sd.ProductLine WHEN 'Access Revenue'
THEN 0<BR> WHEN 'Centrex' THEN
0<BR> WHEN 'CPE' THEN
0<BR> WHEN 'InfoServices' THEN
0<BR> WHEN 'Internet' THEN
0<BR> WHEN 'LecDirectory' THEN
0<BR> WHEN 'LecInternet' THEN
0<BR> WHEN 'Long Distance' THEN
0<BR> WHEN 'Messaging' THEN
0<BR> WHEN 'Network' THEN
0<BR> WHEN 'Switched' THEN
0<BR> WHEN 'Other' THEN 0 else
sd.Total END) as 'Undefined'</FONT></SPAN></DIV>
<DIV><SPAN class=135150817-29052003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=135150817-29052003><FONT face=Arial color=#0000ff
size=2>--</FONT></SPAN></DIV>
<DIV><SPAN class=135150817-29052003><FONT face=Arial color=#0000ff size=2>The
rest are group by columns. Hope this helps.</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV></SPAN><SPAN class=135150817-29052003><FONT face=Arial color=#0000ff
size=2>Mark</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Anita Smith
[mailto:anita@ddisolutions.com.au]<BR><B>Sent:</B> Wednesday, May 28, 2003
4:35 PM<BR><B>To:</B> accessd@databaseadvisors.com<BR><B>Subject:</B>
[AccessD] Crosstab in Oracle<BR><BR></FONT></DIV>
<DIV class=Section1>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Hi Guys,</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Does anyone on this great list
have any suggestions on how to achieve the equivalent of a crosstab query in
Oracle SQL.</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Any suggestions would be greatly
appreciated.</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"></SPAN></FONT> </P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Anita Smith</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Australia</SPAN></FONT></P></DIV></BLOCKQUOTE></BODY></HTML>
<BR>
<BR>
<P><I><FONT SIZE=2 FACE="Arial">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.</FONT></I></P>