Cool
hrishy, January 03, 2003 - 3:35 am UTC
Hi Tom
I was lookin for..a particular solution..but you came out with a fabolous generic solution...this is really amazing..for sql chaellenged..;-D
Materialised view
kit, February 23, 2003 - 12:35 pm UTC
I did post question and you wanted more info.
what we have is 5 Mat views. the select and from, where is same most. mall difference between them is 2 lines of a where clause
mv1 - and colX = 'A'
2 colx = 'B'
3 colx = 'C'
each one of these matviews get generated different times per day. reconcillations and reports are then driven of these mv.
I was hoping todo dyn sql - create materialistic view when an event gets generated. Is there a way of passing a where clause part of the refresh
If we dont do it dynamically, we'll have 8 views which are the same but few lines of the where clause seperate.
all the mv gets refreshed on a daily basis
please dont publish to the whole world
February 23, 2003 - 12:45 pm UTC
this is already published to the entire world.
So -- question is why you have 8 MVs and not just 1. or even N mv's instead of just one.
You would need to give me a concrete example that would need such a situation. Yes, you can create mv's using dynamic sql -- however, I would question seriously the need to do so. that would be like creating "indexes on the fly". a seriously bad idea.
Help???
Kesavan, April 24, 2004 - 6:01 am UTC
Dear Tom,
How to get a cross tab report?For example If I have a table like
SQL>create table sales(Region varchar2(30),Car_sales number,Bike_sales number);
SQL>insert into sales values('US',2000,4000);
SQL>insert into sales values('Africa',200,100);
SQL>insert into sales values('China',20000,10000);
SQL>insert into sales values('India',10000,20000);
I want a report as
SQL> SELECT .....
REGION CAR_SALES BIKE_SALES TOTAL_SALES
------- ---------- ---------- ------------
US 2000 4000 6000
AFRICA 200 100 300
CHINA 20000 10000 30000
INDIA 10000 20000 30000
TOTAL 32200 34100 66300
Could you please with the query?
Please do reply.
Bye!
April 26, 2004 - 5:05 am UTC
that isn't a cross table - that is one of the "simple" queries.
select region, car_sales, bike_sales, car_sales+bike_sals total_sales
from sales;
The query returns not the same output as required...
Mikhail Tuguzhekov, April 26, 2004 - 10:38 am UTC
Hi Tom.
Your Book is amazing, while there are some mistakes in translation into Russian. It not only brings information to me but also shows some routes to walk and try in the world of Oracle.
I'm afraid, the query in your above followup is not complete.
There is no TOTAL line in its output.
Recently I had to play with ROLLUP and CUBE options for my project. So the object is still interesting to me.
I tried not to use any totals SQL+ can count on client side.
So the nearer (but not exact) query is:
---------------------
SELECT DECODE (GROUPING (region), 1, 'TOTAL', region) "REGION",
SUM (car_sales) car_sales,
SUM (bike_sales) bike_sales,
SUM (car_sales) + SUM (bike_sales) total_sales
FROM sales
GROUP BY ROLLUP (region)
ORDER BY GROUPING (region), region;
---------------------
But there are some "have not needed data" to acomplish the given task exactly. Query returns rows in order of random (may be there is not so with ROLLUP but I prefer to add ORDER BY to be sure). So it is difficult to get results as written by Kesavan. We have to create some additional structures (columns in SALES or helper table or something else) to get desired order exactly.
Also I had to change title for REGION column to deal with name resolution.
Have to say there is no cross-table query. It might be if we didn't know what types of production we had sold and even we didn't realize the number of distinct types of that production.
But it's another task and data structures for it differ from ones written by Kesavan.
April 26, 2004 - 10:43 am UTC
oh -- i see, i missed the "total" line. wasn't clear.
i would probably use a union all for something so simple
select region, car_sales, bike_sales, car_sales+bike_sals total_sales
from sales
union all
select 'total', sum(car_sales), sum(bike_sales), sum(car_sales+bike_sals)
/
OK
Kesavan, April 26, 2004 - 2:22 pm UTC
Dear Tom,
Thanks for putting up a followup there.I waited for this some three days back and I found out a way which runs similar to the one used by the Russian Mikhail.And yet You
had a better way that exhibits your mastery over the subject.We long to have a technical knowledge like you but
will that happen?
Bye!
OK
Anne Maria, April 27, 2004 - 1:10 am UTC
Hi Tom,
What is a crosstab query?I haven't heard this before.Do you
have a sample demo for this?
April 28, 2004 - 11:49 am UTC
it is also known as a "pivot" query.
search for pivot on this site for examples. or -- just read the original answer above, it is a pivot.
What is cross-tab report
Mikhail Tuguzhekov, April 28, 2004 - 4:04 am UTC
Hi, Anna Maria.
I hope Tom wouldn't blame me for this answer.
So. Cross-tab report is usually a report organized as a table (not a DBMS table, just table to print on paper).
Column headers and row haeders are two dimensions.
In cell there are values that are corresponding to their column and row headers.
For example, if we sale some types of production (we don't know what types and how many types we sale) in some countries (we also don't know how many countries we work in) then we can print a report like this:
Bike Car UNKNOWN All Goods
USA 100 400 500 1000
Canada 50 300 750 1100
UNKNOWN 0 200 100 300
All Countries 150 900 1350 2400
That is cross-tab report.
However, we get it from DBMS in other format. And client-side application shows it to us like a table.
There is the script.
----------------
drop table sales;
create table sales (country varchar2(30), goods varchar2(30), amount number(19,2));
insert into sales (country, goods, amount) values ('USA','Bike',50);
insert into sales (country, goods, amount) values ('USA','Bike',50);
insert into sales (country, goods, amount) values ('USA','Car',400);
insert into sales (country, goods, amount) values ('USA',NULL,500);
insert into sales (country, goods, amount) values ('Canada','Bike',50);
insert into sales (country, goods, amount) values ('Canada','Car',300);
insert into sales (country, goods, amount) values ('Canada',NULL,750);
insert into sales (country, goods, amount) values (NULL,'Car',200);
insert into sales (country, goods, amount) values (NULL,NULL,100);
select decode(grouping(country),1,'All countries',nvl(country,'UNKNOWN')) Country,
decode(grouping(goods),1,'All countries',nvl(goods,'UNKNOWN')) Goods,
sum(amount) Amount
from sales
group by cube(country,goods);
---------------
There is the output of the query. Columns 'Country' and 'Goods' are the dimensions. Column 'Amount' is the value. If there is no value for any pair of dimensions then there is no row (I mean DBMS row) returned by the query.
--------------
COUNTRY GOODS AMOUNT
------------------------------ ------------------------------ ----------
Canada Bike 50
Canada Car 300
Canada UNKNOWN 750
Canada All countries 1100
USA Bike 100
USA Car 400
USA UNKNOWN 500
USA All countries 1000
All countries Bike 150
UNKNOWN Car 200
All countries Car 900
UNKNOWN UNKNOWN 100
UNKNOWN All countries 300
All countries UNKNOWN 1350
All countries All countries 2400
15 rows selected
Please, gurus, correct me if I'm wrong.
Mikhail.
P.S. There may be more then two dimensions (or just one). But it's difficult to me to use that kind of analisis in printed form.
Crosstab
ghd, August 09, 2007 - 11:51 pm UTC
Hi Tom,
We have this SQL,
select rn,case rn
when 1 then
acc.account_id || ' ' || 'Authorised Rep (1)'
when 2 then
acc.account_id || ' ' || 'Authorised Rep (2)'
when 3 then
acc.account_id || ' ' || 'Authorised Rep (3)'
when 4 then
acc.account_id || ' ' || 'Billing Address'
end lin
from vault.dim_account acc,
(select rownum rn from all_tables where rownum <= 4 )
where current_record_flag = 'Y'
The table dim_account has millions of records, does it perform better if using UNION ?
August 14, 2007 - 10:54 am UTC
do you want to full scan dim_account 4 times?
I might prefer to use either:
(select 1 rn from dual union all select 2 from dual union all....)
or
(select level l from dual connect by level <= 4)
instead of all_tables......
crosstab
Sam, August 14, 2007 - 7:39 pm UTC
New feature in 11g
abz, August 15, 2007 - 8:15 am UTC
There is a new syntax in SELECT, i.e. PIVOT which I think allows us to write cross tab queries in a single STATIC sql query.
Tom, can you please provide a nice example of it.
Thanks
August 20, 2007 - 1:55 pm UTC
this'll be in my next Oracle Magazine column:
...
Pivot this¿
A frequently asked question on AskTom and virtually every online Oracle forum is "how can I pivot a result set"? For example, suppose I have this query--
SQL > select deptno, job, sum(sal) sal
2 from emp
3 group by deptno, job
4 /
DEPTNO JOB SAL
---------- --------- ----------
20 CLERK 1900
30 SALESMAN 5600
20 MANAGER 2975
30 CLERK 950
10 PRESIDENT 5000
30 MANAGER 2850
10 CLERK 1300
10 MANAGER 2450
20 ANALYST 6000
9 rows selected.
--and I need to have the JOB attributes become the column headings, showing just three rows--for DEPTNO 10, 20 and 30--with columns CLERK, ANALYST and so on. Well, we¿ve been able to do this before Oracle Database 11g, and it would look like Listing 8.
Listing 8: Pivot with DECODE
SQL > select deptno,
2 sum( decode( job, 'CLERK', sal ) ) clerk,
3 sum( decode( job, 'SALESMAN', sal ) ) salesman,
4 sum( decode( job, 'MANAGER', sal ) ) manager,
5 sum( decode( job, 'ANALYST', sal ) ) analyst,
6 sum( decode( job, 'PRESIDENT', sal ) ) president
7 from emp
8 group by deptno
9 order by deptno
10 /
DEPTNO CLERK SALESMAN MANAGER ANALYST PRESIDENT
---------- ---------- ---------- ---------- ---------- ----------
10 1300 2450 5000
20 1900 2975 6000
30 950 5600 2850
Now that certainly works, but it was never considered intuitive or easy. Starting with Oracle Database 11g, there is new PIVOT syntax for this operation, it is documented, it is a bit more intuitive to use, and it is shown in Listing 9.
Listing 9: Pivot with PIVOT
SQL > select *
2 from (select deptno, job, sal
3 from emp ) e
4 pivot( sum(sal) for job in
5 ( 'CLERK', 'SALESMAN', 'MANAGER',
6 'ANALYST', 'PRESIDENT' ) )
7 order by deptno
8 /
DEPTNO 'CLERK' 'SALESMAN' 'MANAGER' 'ANALYST' 'PRESIDENT'
---------- ---------- ---------- ---------- ---------- -----------
10 1300 2450 5000
20 1900 2975 6000
30 950 5600 2850
anybody else
abz, August 17, 2007 - 4:02 am UTC
Can anybody else answer also?
Thanks
What do you mean?
Jay, August 20, 2007 - 2:16 pm UTC
abz guy..
The guru just explained about the 'pivot' function himself! I think it's a pretty neat feature. What else do you want to know?
crosstab query
ghd, August 21, 2007 - 8:46 pm UTC
Tom,
Thank you for your response.
An Oracle developer decided to create a function and called this function from SQL
SELECT pvt.EXTRACT_RECORD,
pvt.MAX_JOB_RUN_ID
FROM TABLE ( pivot_dim_account_contact_fnc (<LAST_JOB_RUN_ID>)) pvt
CREATE OR REPLACE function VAULT_APP.pivot_dim_account_contact_fnc(p_job_run_id number DEFAULT -99999999999999)
return dim_account_contact_type_tab pipelined is
l_dim_account_contact_type dim_account_contact_type := dim_account_contact_type(null,null,null,NULL,NULL);
begin
FOr r_acc IN
(SELECT /*+ PARALLEL(acc,12) */
acc.*, MAX(acc.updated_job_run_id) OVER () max_job_run_id
from DIM_ACCOUNT acc
where current_record_flag = 'Y'
AND (acc.created_job_run_id > p_job_run_id
OR acc.updated_job_run_id > p_job_run_id
)
)
LOOP
FOr r_cnt in 1..4
LOOP
If r_cnt = 1 THEN
-- do something
ELSEIF r_cnt = 2 tnen
-- do something
ELSEIF r_cnt = 3 tnen
-- do something
ELSEIF r_cnt = 4 tnen
-- do something
ENd IF;
pipe row(l_dim_account_contact_type);
ENd LOOp;
ENd LOOP;
return;
end;
Which is your preference ?
TOM's way :
(select level l from dual connect by level <= 4)
or the function way
Thank
August 22, 2007 - 12:36 pm UTC
today, I prefer the connect by level for simplicity sake.
with data as (select ... from dual connect by level <= :n )
select ......
from ..., DATA, ...
Thanks
abz, August 22, 2007 - 6:10 am UTC
Tom, thank you for the detailed answer.
But this seems we still have fixed no. of columns right?, we
cannot have variable no. of columns? I mean why do we have
to hard code the jobs, cann't we leave it and let oracle
decide the no. of columns at runtime.
I think its just a better way of doing what we previously
use to do with decode.
Jay, read the date when I asked someone else to answer
and the date tom answered it, actually tom did not answer
it for 2,3 days so I asked if someone else can answer.
Thanks
August 22, 2007 - 1:45 pm UTC
it isn't up to Oracle, it is up to SQL - the standard we deliver.
and ABZ - read the other places you've done the "please read this", I've also asked you to "please stop doing that"
Follow-up on 11g version of pivot
John, March 05, 2008 - 1:54 pm UTC
Regarding the example of 11g's new syntax from Listing 9
select *
from (select deptno, job, sal
from emp ) e
pivot( sum(sal) for job in
( 'CLERK', 'SALESMAN', 'MANAGER',
'ANALYST', 'PRESIDENT' ) )
order by deptno
/
I don't yet have an 11g instance or I'd test it myself, but I'm wondering if something like this is allowed which would address ABZ's last question about dynamically building the column list:
select *
from (select deptno, job, sal
from emp ) e
pivot( sum(sal) for job in
(select distinct job
from emp ) )
order by deptno
/
March 06, 2008 - 7:39 am UTC
no, it will not - the list must be static. SQL needs to know how many columns something will have at parse time and that must remain constant.
11g pivot with dynamic in-list
Barbara Boehemr, March 06, 2008 - 11:07 am UTC
The following demonstrates the 11g pivot using a dynamic in-list and Tom's stragg function or whatever string aggregate method you prefer. The only drawback is that it does not bind the in-list, just concatenates, but at least it accomplishes the dynamic pivot. It is the only workaround that I have found thus far for the static in-list requirement on the new 11g pivot.
SCOTT@orcl_11g> variable g_ref refcursor
SCOTT@orcl_11g> declare
2 v_jobs varchar2 (32767);
3 v_sql varchar2 (32767);
4 begin
5 select stragg (jobs)
6 into v_jobs
7 from (select distinct '''' || job || '''' as jobs
8 from emp);
9 v_sql :=
10 'select *
11 from (select deptno, job, sal
12 from emp)
13 pivot (sum (sal) for job in (' || v_jobs || '))
14 order by deptno';
15 open :g_ref for v_sql;
16 end;
17 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11g> print g_ref
DEPTNO 'ANALYST' 'CLERK' 'MANAGER' 'PRESIDENT' 'SALESMAN'
---------- ---------- ---------- ---------- ----------- ----------
10 1300 2450 5000
20 6000 1900 2975
30 950 2850 5600
SCOTT@orcl_11g>
Cross Tab for a mark Tables
joines, October 08, 2008 - 12:31 pm UTC
I have a table with the marks of the students , here the Marks would be stored in following format like
-------------------------------------------------------------
ResultId | StudentId|SubjectId |Mark|Avg|Status|Comments
--------------------------------------------------------------
1 101 13001 89 45.9 Pass Good
2 101 13002 50 25.5 Pass Good
______________________________________________________________
Now i need to construct this table by reffering other tables
like
i would display the student Details with the Mark List as folllows
Name AdmissionNo RegisterNo Gender ExamName\
---------------------------------------------------------
Joines s021 10023 Male | Unit Test
================================== |____________________
S1 | S2| Mark Avg Com
Is it possible to construct such format , if possible i request you to help me out , i really in need of such query,
if my question is wrong or not understandable , i apologize please help me out and tell me to construct such table format or something similar to that of
A reader, April 01, 2009 - 11:29 pm UTC
Hi
select * from t
A1 B1 C1 D1
a1 b1 c1 d1
a1 b1 c1 d2
a1 b1 c1 d3
a2 b2 c2 d4
a2 b2 c2 d5
a3 b3 c3 d6
I am getting the below error when executing the below sql
ORA-00904:"D":Invalid identifier
select max(decode( ceil(r/3), 1, data, null )),
max(decode( ceil(r/3), 2, data, null )),
max(decode( ceil(r/3), 3, data, null )),
max(decode( ceil(r/3), 4, data, null )),
max(decode( ceil(r/3), 5, data, null ))
from (
select data, rownum-.9 r
from ( select distinct decode( r, 1, a, 2, b, 3, c, 4, d ) data
from t,
(select rownum r from all_objects where rownum <= 4)
order by 1)
)
group by mod( r, 3 )
Thanks,
MR
April 02, 2009 - 9:35 am UTC
of course you are. you have no columns named D, you have one called D1, but not D.
if you want me to look in the future, you really need to provide a create table, insert statements - else - I won't touch it.
cross tab query
Ravi, April 06, 2010 - 6:25 pm UTC
Hi Tom,
Could you please help me with this.
I have following table/data
create table subs (service_name varchar2(10),hits number,status varchar2(1),owner varchar2(10),subscriber varchar2(10))
insert into subs values ('service1',100,'Y','owner1','subs1');
insert into subs values ('service1',200,'Y','owner1','subs2');
insert into subs values ('service1',300,'N','owner1','subs3');
insert into subs values ('service1',300,'N','owner1',null);
insert into subs values ('service2',400,'Y','owner2','subs4');
insert into subs values ('service2',500,'Y','owner2','subs5');
insert into subs values ('service2',600,'Y','owner2','subs6');
insert into subs values ('service3',700,'N','owner3','subs3');
insert into subs values ('service3',800,'Y','owner3','subs5');
insert into subs values ('service3',900,'N','owner3','subs6');
I wanted to concatenate all the subscribers for a service and show the string for each row of the service.
service1 100 Y owner1 'subs1,subs2,subs3'
service1 200 Y owner1 'subs1,subs2,subs3'
service1 300 N owner1 'subs1,subs2,subs3'
service1 300 N owner1 'subs1,subs2,subs3'
service2 400 Y owner2 'subs4,subs5,subs6'
service2 500 Y owner2 'subs4,subs5,subs6'
service2 600 Y owner2 'subs4,subs5,subs6'
service3 700 N owner3 'subs3,subs5,subs6'
service3 800 Y owner3 'subs3,subs5,subs6'
service3 900 N owner3 'subs3,subs5,subs6'
Thanks!
April 12, 2010 - 7:41 pm UTC
search this site for stragg
ops$tkyte%ORA11GR2> select service_name, hits, status, owner, stragg(subscriber) over (partition by service_name) s
2 from subs
3 order by service_name
4 /
SERVICE_NA HITS S OWNER S
---------- ---------- - ---------- ----------------------------------------
service1 100 Y owner1 subs1,subs2,subs3
service1 200 Y owner1 subs1,subs2,subs3
service1 300 N owner1 subs1,subs2,subs3
service1 300 N owner1 subs1,subs2,subs3
service2 400 Y owner2 subs4,subs6,subs5
service2 600 Y owner2 subs4,subs6,subs5
service2 500 Y owner2 subs4,subs6,subs5
service3 800 Y owner3 subs5,subs3,subs6
service3 700 N owner3 subs5,subs3,subs6
service3 900 N owner3 subs5,subs3,subs6
10 rows selected.
that is one approach.
ops$tkyte%ORA11GR2> select service_name, hits, status, owner, max(sys_connect_by_path(subscriber,',')) over(partition by service_name) s
2 from (select subs.*, row_number() over (partition by service_name order by subscriber) rn from subs)
3 start with rn = 1
4 connect by prior service_name = service_name and prior rn+1 = rn
5 order by service_name
6 /
SERVICE_NA HITS S OWNER S
---------- ---------- - ---------- ----------------------------------------
service1 100 Y owner1 ,subs1,subs2,subs3,
service1 200 Y owner1 ,subs1,subs2,subs3,
service1 300 N owner1 ,subs1,subs2,subs3,
service1 300 N owner1 ,subs1,subs2,subs3,
service2 400 Y owner2 ,subs4,subs5,subs6
service2 600 Y owner2 ,subs4,subs5,subs6
service2 500 Y owner2 ,subs4,subs5,subs6
service3 800 Y owner3 ,subs3,subs5,subs6
service3 700 N owner3 ,subs3,subs5,subs6
service3 900 N owner3 ,subs3,subs5,subs6
10 rows selected.
is another.
many thanks
Ravi, April 12, 2010 - 10:58 pm UTC
Not even a day goes by without learning something new from your web site.
Thanks Tom!
stragg
A reader, April 13, 2010 - 11:52 am UTC
Hi Tom,
Somehow max(sys_connect_by_path(subscriber,',')) over(partition by service_name) gives null values for me.
Any known issue with Oracle 9i?
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for Solaris: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production
April 14, 2010 - 7:53 am UTC
ops$tkyte%ORA9IR2> create or replace type string_agg_type as object
2 (
3 total varchar2(4000),
4
5 static function
6 ODCIAggregateInitialize(sctx IN OUT string_agg_type )
7 return number,
8
9 member function
10 ODCIAggregateIterate(self IN OUT string_agg_type ,
11 value IN varchar2 )
12 return number,
13
14 member function
15 ODCIAggregateTerminate(self IN string_agg_type,
16 returnValue OUT varchar2,
17 flags IN number)
18 return number,
19
20 member function
21 ODCIAggregateMerge(self IN OUT string_agg_type,
22 ctx2 IN string_agg_type)
23 return number
24 );
25 /
Type created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create or replace type body string_agg_type
2 is
3
4 static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
5 return number
6 is
7 begin
8 sctx := string_agg_type( null );
9 return ODCIConst.Success;
10 end;
11
12 member function ODCIAggregateIterate(self IN OUT string_agg_type,
13 value IN varchar2 )
14 return number
15 is
16 begin
17 self.total := self.total || ',' || value;
18 return ODCIConst.Success;
19 end;
20
21 member function ODCIAggregateTerminate(self IN string_agg_type,
22 returnValue OUT varchar2,
23 flags IN number)
24 return number
25 is
26 begin
27 returnValue := ltrim(self.total,',');
28 return ODCIConst.Success;
29 end;
30
31 member function ODCIAggregateMerge(self IN OUT string_agg_type,
32 ctx2 IN string_agg_type)
33 return number
34 is
35 begin
36 self.total := self.total || ctx2.total;
37 return ODCIConst.Success;
38 end;
39
40
41 end;
42 /
Type body created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> CREATE or replace
2 FUNCTION stragg(input varchar2 )
3 RETURN varchar2
4 PARALLEL_ENABLE AGGREGATE USING string_agg_type;
5 /
Function created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select service_name, hits, status, owner, stragg(subscriber) over (partition by service_name) s
2 from subs
3 order by service_name
4 /
SERVICE_NA HITS S OWNER S
---------- ---------- - ---------- --------------------
service1 100 Y owner1 subs1,subs2,subs3
service1 200 Y owner1 subs1,subs2,subs3
service1 300 N owner1 subs1,subs2,subs3
service1 300 N owner1 subs1,subs2,subs3
service2 400 Y owner2 subs4,subs5,subs6
service2 500 Y owner2 subs4,subs5,subs6
service2 600 Y owner2 subs4,subs5,subs6
service3 700 N owner3 subs3,subs5,subs6
service3 800 Y owner3 subs3,subs5,subs6
service3 900 N owner3 subs3,subs5,subs6
10 rows selected.
ops$tkyte%ORA9IR2> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for Linux: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
stragg
A reader, April 14, 2010 - 10:57 am UTC
Hi Tom,
I could make the query work with your stragg routine. I was trying out the other query you gave me which is not working on 9i.
Thanks,
Ravi
April 14, 2010 - 3:55 pm UTC
I'd bug that one with metalink please.
ops$tkyte%ORA9IR2> select service_name, hits, status, owner,
2 max(s) over (partition by service_name) ss
3 from (
4 select service_name, hits, status, owner,
5 sys_connect_by_path(subscriber,',') s
6 from (select subs.*, row_number() over (partition by service_name order by subscriber) rn
7 from subs)
8 start with rn = 1
9 connect by prior service_name = service_name and prior rn+1 = rn
10 )
11 order by service_name
12 /
SERVICE_NA HITS S OWNER SS
---------- ---------- - ---------- ----------------------------------------
service1 100 Y owner1 ,subs1,subs2,subs3,
service1 200 Y owner1 ,subs1,subs2,subs3,
service1 300 N owner1 ,subs1,subs2,subs3,
service1 300 N owner1 ,subs1,subs2,subs3,
service2 400 Y owner2 ,subs4,subs5,subs6
service2 500 Y owner2 ,subs4,subs5,subs6
service2 600 Y owner2 ,subs4,subs5,subs6
service3 700 N owner3 ,subs3,subs5,subs6
service3 800 Y owner3 ,subs3,subs5,subs6
service3 900 N owner3 ,subs3,subs5,subs6
10 rows selected.
Still a bit confused...
Matt, May 28, 2010 - 11:39 am UTC
Hi Tom,
I'm still a bit confused by this concept of doing a cross-tab query, and please correct me if what I'm asking isn't the same as what you're explaining here...
I have a query like this:
SELECT
M.CTN,
M.YYYYMM,
F.FEMTO,
M.HC,
M.CHURN,
(quite a few more columns...)
from tableName m
LEFT JOIN anotherTableName ON f.ctn = m.ctn
LEFT JOIN anotherTableName p ON m.ctn = p.ctn
WHERE m.CTN in ('555-555-5555') order by m.YYYYMM DESC
Which produces results like this:
CTN YYYYMM FEMTO HC CHURN
------------------------------------------------------------
55555555555 201003 41-41026-05966 15
555555555555 201002 41-41026-05966 12
55555555555 201001 41-41026-05966 15
5555555555 200912 41-41026-00226 13
Basically, I need to see the results like this:
201003 201002 201001 200912
-------------------------------------------------------
CTN 555555555 5555555555 5555555555 5555555555
FEMTO 15 12 15 13
CHURN
ect...
So that original columns become rows, and the YYYYMM values become the column headings. I'm confused about how to do this because there isn't any aggregation of values, because they are (mostly) all varchar2 values. There are probably 30-40 columns in the original query, and they need to all pivot into the first column and be "grouped" by the YYYYMM headings...
Hope that makes sense, thank you so much for your help!!
Matt
May 28, 2010 - 11:50 am UTC
you have your query, let us call it "Q"
You have this column CTN you would like to go "across" the page based on YYYYMM (I hate that, someone might be tricked into thinking you have a year and a month in that string or number, which we know it cannot be since a year and month would be in a date - but I digress), with everything else going "down"
You are in 10g or before (because in 11g you have pivot and would be using that of course...)
you would:
select <everything else>,
max( decode( yyyymm, 201003, ctn ) ) "201003",
max( decode( yyyymm, 201002, ctn ) ) "201002",
....
max( decode( yyyymm, 200912, ctn ) ) "200912"
from (Q)
group by <everything else>
/
Still a bit confused...
Matt, May 28, 2010 - 12:23 pm UTC
Thank you for the quick response! I don't think I explained what I need the results to look like very well, but you did answer what it probably sounded like, and that made sense...
And you're right about the 10g and the YYYYMM column is crazy... it is a year-month combo, but interally, everyone understands that... very strange, I agree.
I do need the CTN column to go across, but I don't know how many YYYYMM values I will have. Also, I need the other column names in the query to repeat down the resultset in the first column.
Let me try to illustrate it again with a subset of the data...
So, my query returns results like this
YYYYMM CTN CALLS CHURN_VULN SUBMARKET
------ --- ----- ---------- -----------
201003 555-555-5555 295 5 Atlanta
201004 555-555-5555 340 8 Atlanta
201005 555-555-5555 130 2 Atlanta
...
I need to see the CTN column name, CALLS column name, CHURN_VULN column name, and SUBMARKET column name (and all the rest of the 30+ columns in the original query) in the first column of the result set, and then see the values of the original YYYYMM column as columns next to them, like this:
ORIGINAL_COLUMN_NAME 201003 201004 201005...
--------------------- ------ ------ ------
"CTN" 5555555555 5555555555 5555555555
"CALLS" 295 340 130
"CHURN_VULN" 5 8 2
"SUBMARKET" Atlanta Atlanta Atlanta
Does that make sense? As I'm typing this, I'm starting to think this may not be possible with a query, and may require some coding of a proc or function...
Thanks again for your help on this!
Matt
May 28, 2010 - 12:33 pm UTC
... I do need the CTN column to go across, but I don't know how many YYYYMM values I will have ...
you will need to figure that out - you sort of need to know how many columns you will have, and what their names are *before* you parse the query.
If you don't know, you'll have to
a) run a query to figure it out - select distinct yyyymm from t;
b) dynamically construct a query that has references to those values in it
Cross Tab Query for Oracle 9i and above.
Diganta Das, May 31, 2010 - 1:29 am UTC
Hi Tom,
You have been a great inspiration for me.
In my little experience I have applied all the knowledge, I gained from the Oracle documentations and your books to resolve most of the issues, I encountered.
Yesterday night, just saw your reply, to the above query, however it did not satisfied me to the fullest.
I had a hard thought over it :) and finally, now I could answer it.
In order to have horizontal view, I needed few things.
1. A relationship between the rows for the key (Here the first three columns are acting as a key so I created the composite key).
2. A marker, which marks the row to be shown.
Now, in order to figure out the relationship, I created pseudo column using LAG function.
This column allowed me to have a relationship between the current row and the previous row.
Initially I had:-
KEY VAL RNUM
------------- --- ----------
A1 B1 C1 D1 1
A1 B1 C1 D2 2
A1 B1 C1 D3 3
A2 B2 C2 D4 4
A2 B2 C2 D5 5
A3 B3 C3 D6 6
After using the lag function I have the below.
KEY VAL LAG RNUM
------------- --- ---------- ----------
A1 B1 C1 D1 0 1
A1 B1 C1 D2 1 2
A1 B1 C1 D3 2 3
A2 B2 C2 D4 0 4
A2 B2 C2 D5 4 5
A3 B3 C3 D6 0 6
The marker (M_ROW) was provided by the ROW_NUMBER function, Since I need the last child of the tree for display.
Now, I used the HIREARCHIAL query to generate the relationship and SYS_CONNECT_BY_PATH to genrate the view.
Below is the query that generates the view.
COL HVIEW FORMAT A90
WITH TT AS (
SELECT
KEY || ' ' || LTRIM(SYS_CONNECT_BY_PATH(VAL,' '),' ') HVIEW,
RNUM,
M_ROW
FROM
(SELECT
KEY,
VAL,
NVL(LAG(RNUM) OVER(PARTITION BY KEY ORDER BY RNUM),0) PRI_RNUM, --PSUEDO COL ACTING AS PARENT.
RNUM, --PEUDO COL ACTING AS CHILD.
ROW_NUMBER() OVER(PARTITION BY KEY ORDER BY RNUM DESC) M_ROW --MARKER ROW.
FROM (SELECT
COL1 || ' ' || COL2 || ' ' || COL3 KEY, --COMBINING THE FIRST THREE COLUMN TO CREATE A COMPOSITE KEY.
COL4 VAL,
ROWNUM RNUM
FROM T
ORDER BY KEY))
START WITH PRI_RNUM = 0
CONNECT BY PRIOR RNUM = PRI_RNUM)
SELECT
HVIEW
FROM TT
WHERE M_ROW = 1;
HVIEW
--------------------------
A1 B1 C1 D1 D2 D3
A2 B2 C2 D4 D5
A3 B3 C3 D6
--TEST DATA.
------------
--TABLE CREATION STATEMENT.
CREATE TABLE T
(COL1 VARCHAR2(3),
COL2 VARCHAR2(3),
COL3 VARCHAR2(3),
COL4 VARCHAR2(3));
--DUMMY DATA.
INSERT INTO T VALUES ('A1','B1','C1','D1');
INSERT INTO T VALUES ('A1','B1','C1','D2');
INSERT INTO T VALUES ('A1','B1','C1','D3');
INSERT INTO T VALUES ('A2','B2','C2','D4');
INSERT INTO T VALUES ('A2','B2','C2','D5');
INSERT INTO T VALUES ('A3','B3','C3','D6');
--COMMITING THE DATA.
COMMIT;
The only limitation is the usage of SYS_CONNECT_BY_PATH function.
Do you think, I am missing something else.
Thanks
Diganta Das.
May 31, 2010 - 8:24 am UTC
you didn't need the lag.
but since strings are limited to 4000 bytes, you could easily simplify this - you just need rownum by 'key' and a max decode for everything else.
select "key",
max(decode( rn, 1, "rest" )) || ' ' ||
max(decode( rn, 2, "rest" )) || ' ' ||
...
max(decode( rn, N, "rest" )) therest,
case when rn > N then 'truncated...' end msg
from
(
select "key", "rest", row_number() over (partition by key order by something)rn
from t
)
group by "key"
where N = 4000/length(rest)
where length(rest) is at least the average of the length of rest - but if it is the average - realize that your query will fail if you have a set of data that exceeds the average or length = max(length(rest)) in which case will the string concatenation will not fail - but the result might be truncated
Still little confused.
Naveen, September 29, 2010 - 12:38 am UTC
I also have the same situation as Matt has.
September 29, 2010 - 8:45 am UTC
then read the answer I gave matt, it would not be any different for you.
sys_connect_by_path
Ravi B, February 02, 2011 - 4:07 pm UTC
Tom,
Could you please help me with this query.
I have a master table (table1) and detail table table2.
I wanted to list all the columns in table1 and list detail column as comma separated list.
drop table table1;
create table table1 (id number);
insert into table1 values(1);
insert into table1 values(2);
insert into table1 values(3);
insert into table1 values(4);
insert into table1 values(5);
drop table table2;
create table table2 (id2 number,descr varchar2(10));
insert into table2 values(1,'descr1');
insert into table2 values(1,'descr2');
insert into table2 values(2,'descr3');
insert into table2 values(2,'descr4');
Expected result:
id descr
-- -----
1 descr1,descr2
2 descr3,descr4
3 descr not defined
4 descr not defined
5 descr not defined
Following query doesn't give the right results. May be connect by prior is still a mystery to me.
select id,description
from (
select id, LTRIM(max(descr) over (partition by id),',') description,rn
from (
select id,
sys_connect_by_path(descr,',') descr,rn
from (select a.id,nvl(b.descr,'descr not defined') descr, row_number() over (partition by b.id2 order by b.descr) rn
from table1 a,table2 b
where a.id=b.id2(+)
)
start with rn = 1
connect by prior id = id and prior rn+1 = rn
)
order by id
)
where rn=1;
Thanks
February 03, 2011 - 2:59 pm UTC
ops$tkyte%ORA11GR2> select id, nvl( substr( max( sys_connect_by_path( descr, ',' ) ), 2 ), 'descr not defined' ) scbp
2 from (
3 select table1.id, table2.descr,
4 row_number() over (partition by table1.id order by table2.descr) rn
5 from table1, table2
6 where table1.id = table2.id2(+)
7 )
8 start with rn = 1
9 connect by prior id = id and prior rn+1 = rn
10 group by id
11 /
ID SCBP
---------- ------------------------------
1 descr1,descr2
2 descr3,descr4
4 descr not defined
5 descr not defined
3 descr not defined
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select table1.id, nvl(listagg(table2.descr,',') within group (order by table2.descr),'descr not defined') scbp
2 from table1, table2
3 where table1.id = table2.id2(+)
4 group by table1.id
5 /
ID SCBP
---------- ------------------------------
1 descr1,descr2
2 descr3,descr4
3 descr not defined
4 descr not defined
5 descr not defined
listagg is new in 11g.
sys_connect_by_path
Ravi B, February 02, 2011 - 4:29 pm UTC
Got the answer.
row_number() over (partition by b.id2 order by b.descr)
should be
row_number() over (partition by a.id order by b.descr)
Thanks
Help converting to sql
Martin kagz, May 20, 2014 - 10:47 am UTC
Please could you kindly assist with an sql equivalent of your first unreviewed answer that has dynamic rows.