Can I do cross-tab query in Oracle
circetsui
query?
Thanks in advance
Ling
Lothar Armbüster
on the subject Can I do cross-tab query in Oracle:
>Can anybody tell me if there is speical form of SQL in Oracle for
>cross-tab query?
Well, there is some special syntax concerning outer joins:
select * from tab_a,tab_b
where
tab_a.field1=tab_b.field1(+);
Would result in all rows from tab_a and tab_b with equal field1 and
additionally the rows of tab_a where there is no corresponding row in
tab_b.
Using Ansi SQL (?) this would be achieved by the right outer join syntax
(I think :-)
(I'm not very familiar that kind of syntax)
Hope that helps,
Lothar
--
Lothar Armbrüster | lothar.ar...@rheingau.netsurf.de
Hauptstr. 26 | lothar.ar...@t-online.de
D-65346 Eltville |
Jan-Erik Rosinowski
>query?
take this example where i'm converting WERTs to columns, dependant on
the value of ATTRIBUTCODE. the hint enforces the usage of the index on
(dokumentid,attributcode,wert)
SELECT /*+ INDEX(T_DOKUMENTATTRIBUTWERTE I_DIDACWERT) */
DOKUMENTID,
MAX(DECODE(ATTRIBUTCODE,'KUNDECODE',WERT)) KUNDECODE,
MAX(DECODE(ATTRIBUTCODE,'NAME1',WERT)) NAME1,
MAX(DECODE(ATTRIBUTCODE,'NAME2',WERT)) NAME2,
MAX(DECODE(ATTRIBUTCODE,'NAME3',WERT)) NAME3,
MAX(DECODE(ATTRIBUTCODE,'ANSCHRIFT1',WERT)) ANSCHRIFT1,
MAX(DECODE(ATTRIBUTCODE,'ANSCHRIFT2',WERT)) ANSCHRIFT2,
MAX(DECODE(ATTRIBUTCODE,'STAAT',WERT)) STAAT,
MAX(DECODE(ATTRIBUTCODE,'PLZ',WERT)) PLZ,
MAX(DECODE(ATTRIBUTCODE,'ORT',WERT)) ORT,
MAX(DECODE(ATTRIBUTCODE,'ANSPRECHPARTNER',WERT)) ANSPRECHPARTNER,
MAX(DECODE(ATTRIBUTCODE,'ANREDETEXT',WERT)) ANREDETEXT,
MAX(DECODE(ATTRIBUTCODE,'TELEFONZENTRALE',WERT)) TELEFONZENTRALE,
MAX(DECODE(ATTRIBUTCODE,'TELEFONDIREKT',WERT)) TELEFONDIREKT,
MAX(DECODE(ATTRIBUTCODE,'FAX',WERT)) FAX,
MAX(DECODE(ATTRIBUTCODE,'BEMERKUNG',WERT)) BEMERKUNG
FROM
DOSSIER.T_DOKUMENTATTRIBUTWERTE
--WHERE DOKUMENTID=22220
WHERE
ATTRIBUTCODE IN (
'KUNDECODE','NAME1','NAME2','NAME3','ANSCHRIFT1','ANSCHRIFT2','STAAT','PLZ','ORT',
'ANSPRECHPARTNER','ANREDETEXT','TELEFONZENTRALE','TELEFONDIREKT','FAX','BEMERKUNG'
)
GROUP BY
DOKUMENTID
ciao, jan
rs&p-Dossier, die Software zur Erstellung technischer Dokumentationen
und Schriftgutes in Verwaltung und Industrie.
Billy Verreynne
>Can anybody tell me if there is speical form of SQL in Oracle for cross-tab
>query?
You do not need a "special form" of SQL - plain vanilla flavoured ANSI 92
SQL can do the job fine.
The trick is to create a pivot table, and join the data set with that. The
result set will then be pivoted or cross-tabbed, i.e. you make columns into
rows and rows into columns.
This is best explained with a little example.
YEARLY_RESULTS:
Year Quarter Value
---- ------- -----
94 1 100
94 2 150
94 3 170
94 4 200
95 1 190
95 2 210
95 3 205
95 4 215
OK, you want the data of the above table to be displayed (via a SQL SELECT
statement) as follows:
Year Q1 Q2 Q3 Q4
---- --- --- --- ---
94 100 150 170 200
95 190 210 205 215
How to do it? First you need to create (what I call) a pivot table. We want
to pivot the QUARTER column and for a quarter have 4 distinct values. So we
create the following table:
PIVOT_TABLE:
Quarter Q1 Q2 Q3 Q4
------- -- -- -- --
1 1 0 0 0
2 0 1 0 0
3 0 0 1 0
4 0 0 0 1
For Quarter One, only Q1 is "valid" and the others are "invalid". Thus we
mark the Q1 column for Quarter 1 with a one and the other columns with
zeros. Ditto for the other columns (reminiscent of bitmasking).
OK, now we join our data set with the pivot table.
SELECT
yearly_results.year,
SUM( yearly_results.value * pivot_table.q1) "Q1",
SUM( yearly_results.value * pivot_table.q2) "Q2",
SUM( yearly_results.value * pivot_table.q3) "Q3",
SUM( yearly_results.value * pivot_table.q4) "Q4"
FROM yearly_results,
pivot_table
WHERE yearly_results.quarter = pivot_table.quarter
GROUP BY
yearly_results.year
The concept here is that we create 4 brand new columns - one for every
quarter. Then we sum the values from the yearly results table for each of
these 4 columns. However, we do not want to add quarter 1's value into the
wrong columns. Enter the pivot table - it causes quarter 1's value to be
multiplied with zero for any other quarter except quarter 1.
I hope that this example is clear enough (and correct - it has been a while
since I last used it myself! ;-). I found it a bit difficult to grasp it
myself at first, but as soon as the light goes on upstairs, it is actually a
very simple and elegant solution.
regards,
Billy