April 4, 2001 - 8am Central time zone
Reviewer: Vishaka from India
Excellent
July 6, 2001 - 6am Central time zone
Reviewer: Andy from Kuala Lumpur, Malaysia
Turning a table on its side couldn't be easier all of a sudden!
Good Tip
November 1, 2001 - 6am Central time zone
Reviewer: Gupta Gaurav Sharan from Pune, India
It is really worth to learn this trick.
pivot a result set
January 20, 2002 - 5pm Central time zone
Reviewer: A reader
yes this really helped me in solving my problem.
Thanks again
Maria
Can we do it in single with oracle 8.1.7 ?
March 10, 2002 - 2pm Central time zone
Reviewer: A reader
Cross Tab
April 5, 2002 - 4am Central time zone
Reviewer: A reader from UK
Your example of pivoting a result is very useful.
I've tried using the example but am getting a problem. I have a table which looks like,
CODE NAME ID AVG_VAL
1 Wokingham 1 95.5555555555556
1 Wokingham 2 92.156862745098
1 Wokingham 3 86.1971830985915
1 Wokingham 4 82.2222222222222
1 Wokingham 5 81.6666666666667
2 London 1 20
2 London 2 100
2 London 3 20
I want to get a single row based on the code and name, thefore looking like
CODE NAME 1 2
1 Wokingham 95.55555555555561 92.156862745098
..
..
When I try to collapse the rows by code and name I get 'not a GROUP BY expression'.
select code, name
,DECODE (id, 1, avg_val, NULL) c1
,DECODE (id, 2, avg_val, NULL) c2
from tmp_values
group by code, name
Followup April 5, 2002 - 9am Central time zone:
select code, name, MAX( decode ... ), MAX( decode.... )
from tmp_values
group by code, name
runtime value
April 23, 2002 - 10am Central time zone
Reviewer: reader
Hi tom,
in your example above you know that there are 3 (fix) values in the table a 'T' , a 'A' and a "L"
so we just put them in the decode function and returning the values.
in my senerio
1.) I will come to konw this value at run time
2.) for every key there are not same # of options
can we still do it with decode ?
Followup April 23, 2002 - 10am Central time zone:
You will be using a report writing tool to do this then.
SQL means you KNOW the number of columns.
You do not know the number of columns until the data is fetched.
(i do show a way to do this in my book using dynamic sql and ref cursors -- it is in the anlytic
functions chapter)
Thanks
April 23, 2002 - 12pm Central time zone
Reviewer: reader
Thanks, Tom
I have your book. I will try from there !!
can we wrap it
June 10, 2002 - 3pm Central time zone
Reviewer: GajarBajar
Tom
I'm using sqlplus
Your final output above is
KEY T A L
--- - - -
1 T A
2 T A L
3 A
Now my data after pivoting
KEY T A L
--- - - -
1 T A
2 T A L n m n f g h r e w w w ew r t g gh h h h h gf fd
3 A
can I wrap my data for column 2 and display it as
KEY T A L
--- - - -
1 T A
2 T A L n m n f g h r e w w
w ew r t g gh h h
h h gf fd
3 A
my pivot output will be concatanated and not separate columns like yours i.e. the below output is
the result of concatanation. The first column remains the same.
T A L n m n f g h r e w w
w ew r t g gh h h
h h gf fd
I tried using chr(10) . It worked but , the columns follwing this column did not print.
Kindly provide the solution.
Followup June 11, 2002 - 10am Central time zone:
You cannot, why would the L column line up under the T column? SQLPlus won't do that for you, not
at all.
Just what I needed
March 6, 2003 - 9pm Central time zone
Reviewer: Cam Hodge from Perth Australia
I must have spent a few hours trying to work this out, with groups, etc in the end all I needed was
the MAX( clause.
Fantastio Tom!
Tanks
April 17, 2003 - 8am Central time zone
Reviewer: Luca from Italy
Your explanation on pivot drive me to the solution on problem in a view.
Transpose..
July 23, 2003 - 9am Central time zone
Reviewer: vj from in
I have also caught up with a situation of transpose and struggling to implement..
If i fire a query i would get such an result set
MON TUE WED THU FRI SAT SUN
0 1.5 0 1.5 0 0 0
8 0 0 0 0 0 0
0 0.5 0 0 2 0 0
0 0 1 0 0 0 0
0 0 0.5 0 0 0 0
0 0 4 3 1 0 0
0 0 0 3.5 3 0 0
0 0 0 0 2 0 0
0 6 2.5 0 0 0 0
Now i know this timesheet for which week...by having a start date of the week i.e. select
mon,tue,wed,thu,fri,sat,sun from <MYTABLE> where empcode = <MYEMPCODE> and weekstartdate =
'07/01/2003'
The output i am looking is
07/01/2003-0
07/01/2003-8
07/01/2003-0
07/01/2003-0
07/01/2003-0
07/01/2003-0
07/01/2003-0
07/01/2003-0
07/01/2003-0
08/01/2003-1.5
08/01/2003-0
08/01/2003-0.5
08/01/2003-0
08/01/2003-0
08/01/2003-0
08/01/2003-0
08/01/2003-0
08/01/2003-6
..
..
..
TILL SUNDAY
the number of rows in my example is given as 9..it might be more or less than that depends upon the
number of tasks..
Followup July 23, 2003 - 10am Central time zone:
don't understand the data here.
Using Pivot
July 23, 2003 - 10am Central time zone
Reviewer: Amalorpavanathan from INDIA
Hi Tom,
It is very useful for me.
July 24, 2003 - 12am Central time zone
Reviewer: vj from in
SELECT B.WK_SRT_DT,A.MON,A.TUE,A.WED,A.THU,A.FRI,A.SAT,A.SUN FROM TIMESHEETDETAIL A,TIMESHEETMASTER
B
WHERE
A.TIMESHEETID = B.ID AND B.EMPLOYEEID = 1211
AND
B.WK_SRT_DT = TO_DATE('07-01-2002','DD-MM-YYYY')
/
since the alignment is missing i have given you an comma seperated output tom..sorry for the
inconv.
TASKID,MON,TUE,WED,THU,FRI,SAT,SUN
Meeting,0,1.5,0,1.5,0,0,0
Leave,8,0,0,0,0,0,0
Modifyi,0,0.5,0,0,2,0,0
MoM pre,0,0,1,0,0,0,0
Updatin,0,0,0.5,0,0,0,0
Testing,0,0,4,3,1,0,0
Process,0,0,0,3.5,3,0,0
Meeting,0,0,0,0,2,0,0
SQA Aud,0,6,2.5,0,0,0,0
week start date is 07-01-2002 which is monday and 08-01-2002 is tuesday so on and so forth till
sunday..
i want a output like
all monday data
all tuesday data
all wednesday data
...
..
all sunday data
i want to transpose the column to row.
Rgds
August 11, 2003 - 3am Central time zone
Reviewer: vj from in
Hi Tom,
COL1 COL2
==== ====
MFAA IUWY,JKH
MFAAA IUWY,JKH
MFA IUWY
MFAAA COMSC1,PBB
TEST IUWY,JKH
MFB COMSC1,PBB
1 COMSC1
The values in the col2 can grow...I need to pivot that to column.
i.e
MFAA IUWY
MFAA JKH
MFAAA IUWY
MFAAA JKH
...
...
MFB COMSC1
MFB PBB
How this can be achieved considering the values in the column 2 is not known..
Rgds
Followup August 11, 2003 - 7am Central time zone:
version?
Single column into Row
August 11, 2003 - 8am Central time zone
Reviewer: Bipin Ganar from INDIA
Hi Tom,
I have a table "abxx" with column "absg" Number(3)
which is having following rows
absg
--------
1
3
56
232
43
436
23
677
545
367
.
.
.
.
.
.
.
xxxxxx No of rows
How can i get all these values in single row with comma separated?
Like
output_absg
-----------------------------------------------------
1,3,56,232,43,436,23,677,545,367,..,..,..............
Thanx
Bipin G
Followup August 11, 2003 - 9am Central time zone:
search this site for
stragg
version
August 11, 2003 - 8am Central time zone
Reviewer: vj from in
the version is 9ir2 tom.
Rgds
Followup August 11, 2003 - 9am Central time zone:
you too should search for
stragg
pivot
March 23, 2004 - 5pm Central time zone
Reviewer: A reader
SQL> CREATE TABLE T(key number, DAY DATE, VALUE NUMBER);
Table created.
SQL>
SQL> INSERT INTO T VALUES( 1, SYSDATE, 10);
1 row created.
SQL> INSERT INTO T VALUES( 2, SYSDATE -1 , 10);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM T ;
key DAY VALUE
---- --------- ----------
1 23-MAR-04 10
2 22-MAR-04 10
My data looks like above. Some times for a given key I will have one day and some times 31 days
or any number of days in a given month.
Now, the sql I want to write will be provided a given month and the key, and I have to transpose
the data and show all the dates as columns with their respective values. For a given key I
need to have only one row with 30 or 31 date columns( depending on the month the query is running
for)
.
1.How should can I dynamically change the number of columns depending on the month passed. March
will have 31 where as FEB has only 28.
2.Is there a way to transpose this query dynamically instead of writing 31 decode statements.
What is the best way of writing this query.
thanks
Followup March 24, 2004 - 8am Central time zone:
1) you would have to use dynamic sql -- so I would suggest you do not use dynamic sql and you just
always return 31 days (31 columns) with nulls in the columns that have no values. You could also
return a column that tells the program which column is really the "last column"
select max(decode( to_char(day,'dd'),1,value)) d1,
max(decode( to_char(day,'dd'),2,value)) d2,
...
max(decode( to_char(day,'dd'),31,value)) d31,
max( to_char( last_day(:x), 'dd' ) last_col
from t
where key = :a
and day >= trunc(:a)
and day < add_months(trunc(:a),1)
/
2) well, you could write code to dynmically do it in a loop of course, but 31 columns requires,
well, 31 columns.....
Pivot example...
October 16, 2004 - 10pm Central time zone
Reviewer: A reader from Not INDIA
Excellent - this is exactly what I have been looking for. Thanks!!
YAY!
November 10, 2004 - 4am Central time zone
Reviewer: A reader
pivot a result set
May 17, 2005 - 3pm Central time zone
Reviewer: Laurence Eng from Canada
I came across a similar situation at work. The solution I proposed is to use CASE statement to
create the 'sparse' matrix first and then collapsed it. The DECODE statement is definitely more
easier. However, I am very interesting to find out how to automate the creation of all these
DECODE statements for a possible 100+ cases instead of COPY-and-PASTE? Since I am OK with SQL in
general and am relatively new to Oracle PL/SQL, I wonder if anyone who read this could provide some
samples or help. Thank you very much.
My way of doing pivot table is:
create table mytest_tb as
select MID, case when sum(CD_1) > 0 then 'Y'
else 'N' end as CD_1 label='Code 1',
case when sum(CD_2) > 0 then 'Y'
else 'N' end as CD_2 label='Code 2',
case when sum(CD_3) > 0 then 'Y'
else 'N' end as CD_3 label='Code 3',
missing_CD label='Missing CD'
from (select MID, case when EC = 1 then 1
else 0 end as CD_1,
case when CD = 2 then 1
else 0 end as CD_2,
case when CD = 3 then 1
else 0 end as CD_3,
case when count(CD) = 0 then 'Y'
else 'N' end as missing_CD
from mytable
group by MID)
group by MID;
Sample data:
MID CD
123 1
123 2
123 3
222 1
333 2
411 3
456 3
333 1
333 2
789
Followup May 17, 2005 - 4pm Central time zone:
do you have access to expert one on one Oracle? In there I demonstrated a stored procedure that
runs a query to build a query and returns a ref cursor that is the result set.
chapter on dynamic sql I believe (or anaytics)
pivot a result set
May 18, 2005 - 10am Central time zone
Reviewer: Laurence Eng from Canada
Thank you, Tom.
Unfortunately, I do not have access to the Expert one-on-one Oracle book at the moment. I checked
with my local book store last night but could not find a copy. Therefore I have to order it
on-line today after work.
However, thank you for letting me know.
Followup May 18, 2005 - 11am Central time zone:
Here is the relevant portion from the analytics chapter:
....
One other thing we must know is the MAXIMUM number of rows per partition we anticipate. This will
dictate the number of columns we will be generating. Without it - we cannot pivot. SQL needs to
know the number of columns and there is no way around that fact. That leads us into the next more
generic example of pivoting. If we do not know the number of total columns until runtime, we'll
have to use dynamic SQL to deal with the fact that the SELECT list is variable. We can use PL/SQL
to demonstrate how to do this - and end up with a generic routine that can be reused whenever you
need a pivot. This routine will have the following specification:
scott@TKYTE816> create or replace package my_pkg
2 as
3 type refcursor is ref cursor;
4 type array is table of varchar2(30);
5
6 procedure pivot( p_max_cols in number default NULL,
7 p_max_cols_query in varchar2 default NULL,
8 p_query in varchar2,
9 p_anchor in array,
10 p_pivot in array,
11 p_cursor in out refcursor );
12 end;
13 /
Package created.
Here, you must send in either P_MAX_COLS or P_MAX_COLS_QUERY. SQL needs to know the number of
columns in a query and this parameter will allow us to build a query with the proper number of
columns. The value you should send in here will be the output of a query similar to:
scott@TKYTE816> select max(count(*)) from emp group by deptno, job;
That is - it is the count of the discrete values that are currently in ROWS that we will put into
COLUMNS. You can either send in the query to get this number, or the number if you already know
it.
The P_QUERY parameter is simply the query that gathers your data together. Using the last example
from above the query would be:
10 from ( select deptno, job, ename, sal,
11 row_number() over ( partition by deptno, job
12 order by sal, ename ) rn
13 from emp
14 )
The next two inputs are arrays of column names. The P_ANCHOR tells us what columns will stay CROSS
RECORD (down the page) and P_PIVOT states the columns that will go IN RECORD (across the page). In
our example from above, P_ANCHOR = ( 'DEPTNO', 'JOB' ) and P_PIVOT = ('ENAME','SAL'). Skipping
over the implementation for a moment, the entire call put together might look like this:
scott@TKYTE816> variable x refcursor
scott@TKYTE816> set autoprint on
scott@TKYTE816> begin
2 my_pkg.pivot
3 ( p_max_cols_query => 'select max(count(*)) from emp
group by deptno,job',
4 p_query => 'select deptno, job, ename, sal,
5 row_number() over ( partition by deptno, job
6 order by sal, ename ) rn
7 from emp a',
8 p_anchor => my_pkg.array( 'DEPTNO','JOB' ),
9 p_pivot => my_pkg.array( 'ENAME', 'SAL' ),
10 p_cursor => :x );
11 end;
12 /
PL/SQL procedure successfully completed.
DEPTNO JOB ENAME_ SAL_1 ENAME_2 SAL_2 ENAME_3 SAL_3 ENAME_ SAL_4
------ --------- ------ ----- ---------- ----- ---------- ----- ------ -----
10 CLERK MILLER 1300
10 MANAGER CLARK 2450
10 PRESIDENT KING 5000
20 ANALYST FORD 3000 SCOTT 3000
20 CLERK SMITH 800 ADAMS 1100
20 MANAGER JONES 2975
30 CLERK JAMES 99
30 MANAGER BLAKE 99
30 SALESMAN ALLEN 99 MARTIN 99 TURNER 99 WARD 99
9 rows selected.
As you can see - that dynamically rewrote our query using the generalized template we developed.
The implementation of the package body is straightforward:
scott@TKYTE816> create or replace package body my_pkg
2 as
3
4 procedure pivot( p_max_cols in number default NULL,
5 p_max_cols_query in varchar2 default NULL,
6 p_query in varchar2,
7 p_anchor in array,
8 p_pivot in array,
9 p_cursor in out refcursor )
10 as
11 l_max_cols number;
12 l_query long;
13 l_cnames array;
14 begin
15 -- figure out the number of columns we must support
16 -- we either KNOW this or we have a query that can tell us
17 if ( p_max_cols is not null )
18 then
19 l_max_cols := p_max_cols;
20 elsif ( p_max_cols_query is not null )
21 then
22 execute immediate p_max_cols_query into l_max_cols;
23 else
24 raise_application_error(-20001, 'Cannot figure out max cols');
25 end if;
26
27
28 -- Now, construct the query that can answer the question for us...
29 -- start with the C1, C2, ... CX columns:
30
31 l_query := 'select ';
32 for i in 1 .. p_anchor.count
33 loop
34 l_query := l_query || p_anchor(i) || ',';
35 end loop;
36
37 -- Now add in the C{x+1}... CN columns to be pivoted:
38 -- the format is "max(decode(rn,1,C{X+1},null)) cx+1_1"
39
40 for i in 1 .. l_max_cols
41 loop
42 for j in 1 .. p_pivot.count
43 loop
44 l_query := l_query ||
45 'max(decode(rn,'||i||','||
46 p_pivot(j)||',null)) ' ||
47 p_pivot(j) || '_' || i || ',';
48 end loop;
49 end loop;
50
51 -- Now just add in the original query
52 l_query := rtrim(l_query,',')||' from ( '||p_query||') group by ';
53
54 -- and then the group by columns...
55
56 for i in 1 .. p_anchor.count
57 loop
58 l_query := l_query || p_anchor(i) || ',';
59 end loop;
60 l_query := rtrim(l_query,',');
61
62 -- and return it
63 execute immediate 'alter session set cursor_sharing=force';
64 open p_cursor for l_query;
65 execute immediate 'alter session set cursor_sharing=exact';
66 end;
67
68 end;
69 /
Package body created.
It only does a little string manipulation to rewrite the query and open a REF CURSOR dynamically.
In the likely event the query had a predicate with constants and such in it, we set cursor sharing
on and then back off for the parse of this query to facilitate bind variables (see the section on
tuning for more information on that). Now we have a fully parsed query that is ready to be fetched
from.
pivot a result set
May 18, 2005 - 5pm Central time zone
Reviewer: Laurence Eng from Canada
Thank you very much indeed, Tom.
However, I will still add your books to my bookshelf.
Thanks again.
question on number of decodes in a statement????
May 23, 2005 - 1am Central time zone
Reviewer: whizkid from APAC
ran into the following problem when trying to run the following query:
CREATE TABLE WEO_ITR_GEN_PARAM
(
KEY NUMBER,
KEYNAME VARCHAR2(4000),
VALUE1 VARCHAR2(4000),
VALUE2 VARCHAR2(4000),
VALUE3 VARCHAR2(4000)
);
INSERT INTO WEO_ITR_GEN_PARAM ( KEY, KEYNAME, VALUE1, VALUE2, VALUE3 ) VALUES ( 4452, 'Call Start
Time:', '08-NOV-04 17:37:40', NULL, NULL);
INSERT INTO WEO_ITR_GEN_PARAM ( KEY, KEYNAME, VALUE1, VALUE2, VALUE3 ) VALUES ( 4452, 'Call End
Time:', '08-NOV-04 ', NULL, NULL);
INSERT INTO WEO_ITR_GEN_PARAM ( KEY, KEYNAME, VALUE1, VALUE2, VALUE3 ) VALUES ( 4452, 'Name',
'Dinesh Chander Dimri', NULL, NULL);
INSERT INTO WEO_ITR_GEN_PARAM ( KEY, KEYNAME, VALUE1, VALUE2, VALUE3 ) VALUES ( 4452, 'Policy
No./Covernote No..', 'test', NULL, NULL);
INSERT INTO WEO_ITR_GEN_PARAM ( KEY, KEYNAME, VALUE1, VALUE2, VALUE3 ) VALUES ( 4452, 'Contact
No.', '32537668', NULL, NULL);
INSERT INTO WEO_ITR_GEN_PARAM ( KEY, KEYNAME, VALUE1, VALUE2, VALUE3 ) VALUES ( 4452, 'Vehicle
No.', 'DL3CR2130', NULL, NULL);
INSERT INTO WEO_ITR_GEN_PARAM ( KEY, KEYNAME, VALUE1, VALUE2, VALUE3 ) VALUES ( 4452, 'Engine No.',
'--------', NULL, NULL);
INSERT INTO WEO_ITR_GEN_PARAM ( KEY, KEYNAME, VALUE1, VALUE2, VALUE3 ) VALUES ( 4452, 'Chassis
No.', '---------', NULL, NULL);
INSERT INTO WEO_ITR_GEN_PARAM ( KEY, KEYNAME, VALUE1, VALUE2, VALUE3 ) VALUES ( 4452, 'Vehicle
Type', 'Maruti', NULL, NULL);
INSERT INTO WEO_ITR_GEN_PARAM ( KEY, KEYNAME, VALUE1, VALUE2, VALUE3 ) VALUES ( 4452, 'Model',
'Omni', NULL, NULL);
INSERT INTO WEO_ITR_GEN_PARAM ( KEY, KEYNAME, VALUE1, VALUE2, VALUE3 ) VALUES ( 4452, 'Accident
Date', '22/10/2004', NULL, NULL);
INSERT INTO WEO_ITR_GEN_PARAM ( KEY, KEYNAME, VALUE1, VALUE2, VALUE3 ) VALUES ( 4452, 'Accitdent
Time', '3:30:00 PM', NULL, NULL);
INSERT INTO WEO_ITR_GEN_PARAM ( KEY, KEYNAME, VALUE1, VALUE2, VALUE3 ) VALUES ( 4452, 'Date of
Call', '8/11/2004', NULL, NULL);
INSERT INTO WEO_ITR_GEN_PARAM ( KEY, KEYNAME, VALUE1, VALUE2, VALUE3 ) VALUES ( 4452, 'Desc. Of how
the accident took place', 'testdata', NULL, NULL);
INSERT INTO WEO_ITR_GEN_PARAM ( KEY, KEYNAME, VALUE1, VALUE2, VALUE3 ) VALUES ( 4452, 'Body
Injury', 'No', NULL, NULL);
INSERT INTO WEO_ITR_GEN_PARAM ( KEY, KEYNAME, VALUE1, VALUE2, VALUE3 ) VALUES ( 4452, 'TP
Property', 'No', NULL, NULL);
INSERT INTO WEO_ITR_GEN_PARAM ( KEY, KEYNAME, VALUE1, VALUE2, VALUE3 ) VALUES ( 4452, 'Vehicle
Inspection Add.', 'test data', NULL, NULL);
INSERT INTO WEO_ITR_GEN_PARAM ( KEY, KEYNAME, VALUE1, VALUE2, VALUE3 ) VALUES ( 4452, 'Remarks',
'Kindly ', NULL, NULL);
INSERT INTO WEO_ITR_GEN_PARAM ( KEY, KEYNAME, VALUE1, VALUE2, VALUE3 ) VALUES ( 4452, 'Claim
Ref.No.', 'SP-4C-6282', NULL, NULL);
INSERT INTO WEO_ITR_GEN_PARAM ( KEY, KEYNAME, VALUE1, VALUE2, VALUE3 ) VALUES ( 4452, 'Call
Status', 'Open', NULL, NULL);
INSERT INTO WEO_ITR_GEN_PARAM ( KEY, KEYNAME, VALUE1, VALUE2, VALUE3 ) VALUES ( 4452, 'Clients
Location', 'D', NULL, NULL);
INSERT INTO WEO_ITR_GEN_PARAM ( KEY, KEYNAME, VALUE1, VALUE2, VALUE3 ) VALUES ( 4452, 'Directed
To.', 'DC', NULL, NULL);
INSERT INTO WEO_ITR_GEN_PARAM ( KEY, KEYNAME, VALUE1, VALUE2, VALUE3 ) VALUES ( 4452, 'Make', NULL,
NULL, NULL);
COMMIT;
ORA9I >SELECT KEY,
2 MIN(DECODE(KEYNAME,'Accident Date',VALUE1,NULL)) a,
3 MIN(DECODE(KEYNAME,'Accitdent Time',VALUE1,NULL)) b,
4 MIN(DECODE(KEYNAME,'Action To Be Taken By Executiv',VALUE1,NULL)) c,
5 MIN(DECODE(KEYNAME,'Address',VALUE1,NULL)) d,
6 MIN(DECODE(KEYNAME,'Age',VALUE1,NULL)) e,
7 MIN(DECODE(KEYNAME,'Body Injury',VALUE1,NULL)) f,
8 MIN(DECODE(KEYNAME,'CIty',VALUE1,NULL)) g,
9 MIN(DECODE(KEYNAME,'CSE Status',VALUE1,NULL)) h,
10 MIN(DECODE(KEYNAME,'Call Status',VALUE1,NULL)) i,
11 MIN(DECODE(KEYNAME,'Category',VALUE1,NULL)) j,
12 MIN(DECODE(KEYNAME,'Chassis No.',VALUE1,NULL)) k,
13 MIN(DECODE(KEYNAME,'Claim Ref.No.',VALUE1,NULL)) l,
14 MIN(DECODE(KEYNAME,'Claims Product',VALUE1,NULL)) m,
15 MIN(DECODE(KEYNAME,'Clients Location',VALUE1,NULL)) n,
16 MIN(DECODE(KEYNAME,'Complaint Regarding',VALUE1,NULL)) o,
17 MIN(DECODE(KEYNAME,'Contact No.',VALUE1,NULL)) p,
18 MIN(DECODE(KEYNAME,'Damage Date',VALUE1,NULL)) DamageDate,
19 MIN(DECODE(KEYNAME,'Damage Time',VALUE1,NULL)) DamageTime
20 FROM WEO_ITR_GEN_PARAM
21 GROUP BY KEY
22 /
FROM WEO_ITR_GEN_PARAM
*
ERROR at line 20:
ORA-01467: sort key too long
Elapsed: 00:00:00.00
have referred to metalink note nos: 18870.1 & 11309.1 .. but they say problem has been fixed after
ver 7.3. our version is 9.2.0.5.. have tried on 2 different databases (AIX 5.2 & Win 2000).. the
db_block_size is 8192.. is splitting the query the only solution??
Followup May 23, 2005 - 8am Central time zone:
welcome to the world of generic models.
So, do these attributes hidden in this table have realistic lengths. if you:
MIN(DECODE(KEYNAME,'Accident Date', substr(VALUE1,1,20) ,NULL))
substr them to be the right length (I cannot imagine a date needs 4,000 characters), you'll find it
works (and the calling application actually gets meaningful lengths back!!)
for other columns??
May 23, 2005 - 12pm Central time zone
Reviewer: whizkid from APAC
i tried doing a substr on only the date / time columns, but still the same error.. but not i
understand why it's giving the error.. we cant do a substr on all the columns because there are
lots of cols which have description sort of thing stored wherein we wont be able to know how many
characters will it go upto and we need to display everything..
so is this a trade-off for building a generic model?
Followup May 23, 2005 - 3pm Central time zone:
it is giving the error not because anything was fixed in 7, just a limit raised (and in 10g, this
query was ok as another limit was raised).
there are so so so many trade offs for generic models and yes, this is one of them.
for now, if you have few enough rows, you can:
select key,
(select value1 from weo_itr_gen_param where keyname = 'Accident Date' and key = X.key ),
(select value1 from weo_itr_gen_param where keyname = 'Accident Time' and key = X.key ),
...
from (select distinct key from weo_itr_gen_param) X
this one works!
May 24, 2005 - 4am Central time zone
Reviewer: whizkid from APAC
thanks a lot tom... this query also works... its simply amazing how always learn something new
whenever I come to this site.. !
Ive constructed the whole query and ran on the test database and it works fine.. havent yet
executed on production.. the table has 1.3 million rows... does it fall under the "too many rows"??
Ill measure the performance and let you know..
thanks a lot once again!
Followup May 24, 2005 - 8am Central time zone:
it well could -- that scalar subquery would be executed N times for each output row.
Another approach, break the query into two:
with a as
(
SELECT KEY,
MIN(DECODE(KEYNAME,'Accident Date',VALUE1,NULL)) a,
MIN(DECODE(KEYNAME,'Accitdent Time',VALUE1,NULL)) b,
MIN(DECODE(KEYNAME,'Action To Be Taken By Executiv',VALUE1,NULL)) c,
MIN(DECODE(KEYNAME,'Address',VALUE1,NULL)) d,
MIN(DECODE(KEYNAME,'Age',VALUE1,NULL)) e,
MIN(DECODE(KEYNAME,'Body Injury',VALUE1,NULL)) f,
MIN(DECODE(KEYNAME,'CIty',VALUE1,NULL)) g,
MIN(DECODE(KEYNAME,'CSE Status',VALUE1,NULL)) h,
FROM WEO_ITR_GEN_PARAM
GROUP BY KEY
),
b as
(
SELECT KEY,
MIN(DECODE(KEYNAME,'Call Status',VALUE1,NULL)) i,
MIN(DECODE(KEYNAME,'Category',VALUE1,NULL)) j,
MIN(DECODE(KEYNAME,'Chassis No.',VALUE1,NULL)) k,
MIN(DECODE(KEYNAME,'Claim Ref.No.',VALUE1,NULL)) l,
MIN(DECODE(KEYNAME,'Claims Product',VALUE1,NULL)) m,
MIN(DECODE(KEYNAME,'Clients Location',VALUE1,NULL)) n,
MIN(DECODE(KEYNAME,'Complaint Regarding',VALUE1,NULL)) o,
MIN(DECODE(KEYNAME,'Contact No.',VALUE1,NULL)) p,
MIN(DECODE(KEYNAME,'Damage Date',VALUE1,NULL)) DamageDate,
MIN(DECODE(KEYNAME,'Damage Time',VALUE1,NULL)) DamageTime
FROM WEO_ITR_GEN_PARAM
GROUP BY KEY
)
select *
from a full outer join b on ( a.key = b.key )
/
using the full outer join only if needed (if you know all KEY values have say attribute A, it would
be enough to just outer jion. If you know all KEY values have say attributes A and M, you could
make sure A is in 'a' and M is in 'b' and just join)
Pivot all columns
May 24, 2005 - 8am Central time zone
Reviewer: Gerald Koerkenmeier from St. Louis, MO
Tom,
But how can I convert each row to a column like this:
Given this table (or query result set):
PERIOD_NM MEASD_TKT_CNT NON_MEASD_TKT_CNT CKT_CNT
--------- ------------- ----------------- ----------
Jan-03 7 1 4331
Feb-03 12 1 4295
Mar-03 4 0 5126
Apr-03 18 2 5073
How can I pivot every row into a column to get the following (the first column is not necessary,
and obviously the 4 columns will be VARCHAR2 datatype due to the period_nm in each). Is there any
way to do it without a UNION ALL?
Period Jan-03 Feb-03 Mar-03 Apr-03
Measured 7 12 4 18
Non-Measured 1 1 0 2
Circuits 4331 4295 5126 5073
If it makes things easier, I really don't even need the period name. This would allow the pivoted
columns to be NUMBER datatype:
MEASD_TKT_CNT NON_MEASD_TKT_CNT CKT_CNT
------------- ----------------- ----------
7 1 4331
12 1 4295
4 0 5126
18 2 5073
Measured 7 12 4 18
Non-Measured 1 1 0 2
Circuits 4331 4295 5126 5073
Thanks so much!
Followup May 24, 2005 - 10am Central time zone:
no create table
no insert intos
no attempts....
but we'd have to first cartesian join that table with a 3 row table so we can get:
dt key val
jan-03 period 7
jan-03 measured 1
jan-03 circuts 4331
feb-03 period ...
and once you have that use the pivot technique above
select key, max( decode( dt, 'jan-03', val ) ),
max( decode( dt, 'feb-03', val ) ),
....
from (that_query)
group by key
the query above
June 2, 2005 - 12pm Central time zone
Reviewer: whizkid from APAC
hi tom,
this is with respect to the query above.. the original query has 74 decodes.. currently we have
splitted it in 7 views with 7 queries and joined all those 7 views in another view...
after your suggestion, put a substr, used the "with a as" to break into 2 queries / 3 queries.. but
it still gives the same "sort key too long".. is there any other method? using our current method,
it takes around 15 minutes to get the results.. if we break the query so many times , it will take
the same time.. right? so was wondering if you had another alternative to execute the below query?
SELECT KEY,
MIN(DECODE(KEYNAME,'Accident Date',SUBSTR(VALUE1,20),NULL)) AccidentDate,
MIN(DECODE(KEYNAME,'Accitdent Time',SUBSTR(VALUE1,20),NULL)) AccitdentTime,
MIN(DECODE(KEYNAME,'Action To Be Taken By Executiv',SUBSTR(VALUE1,300),NULL))
ActionToBeTakenByExecutive,
MIN(DECODE(KEYNAME,'Address',SUBSTR(VALUE1,500),NULL)) Address,
MIN(DECODE(KEYNAME,'Age',SUBSTR(VALUE1,10),NULL)) Age,
MIN(DECODE(KEYNAME,'Body Injury',SUBSTR(VALUE1,200),NULL)) BodyInjury,
MIN(DECODE(KEYNAME,'CIty',SUBSTR(VALUE1,50),NULL)) City,
MIN(DECODE(KEYNAME,'CSE Status',SUBSTR(VALUE1,20),NULL)) CseStatus,
MIN(DECODE(KEYNAME,'Call Status',SUBSTR(VALUE1,20),NULL)) CallStatus,
MIN(DECODE(KEYNAME,'Category',SUBSTR(VALUE1,50),NULL)) Category,
MIN(DECODE(KEYNAME,'Chassis No.',SUBSTR(VALUE1,50),NULL)) ChassisNo,
MIN(DECODE(KEYNAME,'Claim Ref.No.',SUBSTR(VALUE1,50),NULL)) ClaimRefNo,
MIN(DECODE(KEYNAME,'Claims Product',SUBSTR(VALUE1,10),NULL)) ClaimsProduct,
MIN(DECODE(KEYNAME,'Clients Location',SUBSTR(VALUE1,50),NULL)) ClientsLocation,
MIN(DECODE(KEYNAME,'Complaint Regarding',SUBSTR(VALUE1,50),NULL)) ComplaintRegarding,
MIN(DECODE(KEYNAME,'Contact No.',SUBSTR(VALUE1,50),NULL)) ContactNo,
MIN(DECODE(KEYNAME,'Damage Date',SUBSTR(VALUE1,20),NULL)) DamageDate,
MIN(DECODE(KEYNAME,'Damage Time',SUBSTR(VALUE1,20),NULL)) DamageTime,
MIN(DECODE(KEYNAME,'Date ',SUBSTR(VALUE1,20),NULL)) DateOfEnquiry,
MIN(DECODE(KEYNAME,'Date of Call',SUBSTR(VALUE1,20),NULL)) DateofCall,
MIN(DECODE(KEYNAME,'Date of Loss',SUBSTR(VALUE1,20),NULL)) DateofLoss,
MIN(DECODE(KEYNAME,'Date of Purchase',SUBSTR(VALUE1,20),NULL)) DateofPurchase,
MIN(DECODE(KEYNAME,'Desc. Of how the accident took',SUBSTR(VALUE1,250),NULL)) AccidentDescription,
MIN(DECODE(KEYNAME,'Description OF loss',SUBSTR(VALUE1,250),NULL)) LossDescription,
MIN(DECODE(KEYNAME,'Directed To.',SUBSTR(VALUE1,100),NULL)) DirectedTo,
MIN(DECODE(KEYNAME,'E-mail id',SUBSTR(VALUE1,100),NULL)) EmailId,
MIN(DECODE(KEYNAME,'Call End Time:',SUBSTR(VALUE1,20),NULL)) EndTime,
MIN(DECODE(KEYNAME,'Engine No.',SUBSTR(VALUE1,50),NULL)) EngineNo,
MIN(DECODE(KEYNAME,'I.M.E.I. No.',SUBSTR(VALUE1,50),NULL)) IMEI_No,
MIN(DECODE(KEYNAME,'Life Assured',SUBSTR(VALUE1,100),NULL)) LifeAssured,
MIN(DECODE(KEYNAME,'Location',SUBSTR(VALUE1,50),NULL)) Location,
MIN(DECODE(KEYNAME,'Make',SUBSTR(VALUE1,30),NULL)) Make,
MIN(DECODE(KEYNAME,'Mobile Number',SUBSTR(VALUE1,20),NULL)) MobileNo,
MIN(DECODE(KEYNAME,'Model',SUBSTR(VALUE1,50),NULL)) Model,
MIN(DECODE(KEYNAME,'Name',VALUE1,NULL)) Name,
MIN(DECODE(KEYNAME,'Pincode',SUBSTR(VALUE1,10),NULL)) Pincode,
MIN(DECODE(KEYNAME,'Policy Expiry Date',SUBSTR(VALUE1,20),NULL)) PolicyExpiryDate,
MIN(DECODE(KEYNAME,'Policy Holder',SUBSTR(VALUE1,250),NULL)) PolicyHolder,
MIN(DECODE(KEYNAME,'Policy No.',SUBSTR(VALUE1,50),NULL)) PolicyNo,
MIN(DECODE(KEYNAME,'Policy No./Covernote No..',SUBSTR(VALUE1,50),NULL)) PolicyNo_CovernoteNo,
MIN(DECODE(KEYNAME,'Product Int.',SUBSTR(VALUE1,50),NULL)) ProductInterested,
MIN(DECODE(KEYNAME,'Product Name',SUBSTR(VALUE1,50),NULL)) ProductName,
MIN(DECODE(KEYNAME,'Product Version',SUBSTR(VALUE1,10),NULL)) ProductVersion,
MIN(DECODE(KEYNAME,'Purchase Cost',SUBSTR(VALUE1,20),NULL)) PurchaseCost,
MIN(DECODE(KEYNAME,'Query Regarding',SUBSTR(VALUE1,250),NULL)) QueryRegarding,
MIN(DECODE(KEYNAME,'Recd. Status',SUBSTR(VALUE1,20),NULL)) RecdStatus,
MIN(DECODE(KEYNAME,'Remarks',SUBSTR(VALUE1,250),NULL)) Remarks,
MIN(DECODE(KEYNAME,'Renewal Amount',SUBSTR(VALUE1,20),NULL)) RenewalAmount,
MIN(DECODE(KEYNAME,'Solution Given',SUBSTR(VALUE1,250),NULL)) SolutionGiven,
MIN(DECODE(KEYNAME,'Sr no./Ref no.',SUBSTR(VALUE1,50),NULL)) SrNo_RefNo,
MIN(DECODE(KEYNAME,'Status',SUBSTR(VALUE1,20),NULL)) Status,
MIN(DECODE(KEYNAME,'TP Property',SUBSTR(VALUE1,250),NULL)) TP_Property,
MIN(DECODE(KEYNAME,'Tel no. Res',SUBSTR(VALUE1,20),NULL)) ResidenceTelNo,
MIN(DECODE(KEYNAME,'Tel no.Off',SUBSTR(VALUE1,20),NULL)) OfficeTelNo,
MIN(DECODE(KEYNAME,'Vehicle Inspection Add.',SUBSTR(VALUE1,500),NULL)) VehicleInspectionAdd,
MIN(DECODE(KEYNAME,'Vehicle No.',SUBSTR(VALUE1,20),NULL)) VehicleNo,
MIN(DECODE(KEYNAME,'Vehicle Type',SUBSTR(VALUE1,30),NULL)) VehicleType,
MIN(DECODE(KEYNAME,'Call Start Time:',SUBSTR(VALUE1,20),NULL)) StartTime,
MIN(DECODE(KEYNAME,'Nature of Damages/Loss & Approx. Value of Loss',VALUE1,NULL)) NatureOfDamages,
MIN(DECODE(KEYNAME,'Immediate Contact No.',SUBSTR(VALUE1,20),NULL)) ImmediateContactNo,
MIN(DECODE(KEYNAME,'Name & Address of Consigner',SUBSTR(VALUE1,500),NULL)) NameAddressOfConsigner,
MIN(DECODE(KEYNAME,'Mode Of Transportation',SUBSTR(VALUE1,50),NULL)) ModeOfTransportation,
MIN(DECODE(KEYNAME,'Full description of goods and their value',SUBSTR(VALUE1,250),NULL))
FullDescriptionOfGoods,
MIN(DECODE(KEYNAME,'Location of loss',SUBSTR(VALUE1,250),NULL)) LocationOfLoss,
MIN(DECODE(KEYNAME,'Contact person name',SUBSTR(VALUE1,250),NULL)) ContactPersonName,
MIN(DECODE(KEYNAME,'Policy Issue Office',SUBSTR(VALUE1,50),NULL)) PolicyIssueOffice,
MIN(DECODE(KEYNAME,'Name & Address ofConsignee',SUBSTR(VALUE1,500),NULL)) NameAddressOfConsignee,
MIN(DECODE(KEYNAME,'Accident Date',SUBSTR(VALUE1,20),NULL)) AccidentDate,
MIN(DECODE(KEYNAME,'Accitdent Time',SUBSTR(VALUE1,20),NULL)) AccidentTime,
MIN(DECODE(KEYNAME,'Item Damage',SUBSTR(VALUE1,500),NULL)) ItemDamage,
MIN(DECODE(KEYNAME,'Cause of Loss',SUBSTR(VALUE1,50),NULL)) CauseOfLoss,
MIN(DECODE(KEYNAME,'Inspection Address',SUBSTR(VALUE1,50),NULL)) InspectionAddress,
MIN(DECODE(KEYNAME,'Estimate Loss',SUBSTR(VALUE1,50),NULL)) EstimateLoss,
MIN(DECODE(KEYNAME,'Media Details',SUBSTR(VALUE1,50),NULL)) MediaDetails
FROM WEO_ITR_GEN_PARAM
where key = 212199
group by KEY
thanks tom..
Pivoting x rows in columns
June 28, 2005 - 6pm Central time zone
Reviewer: Mita from NJ
I have following table structure
create table price (id number, price_date date, price number);
Insert into price(id, price, price_date) Values(1, 1, trunc(sysdate));
Insert into price(id, price, price_date) Values(2, 2, trunc(sysdate));
Insert into price(id, price, price_date) Values(3, 3, trunc(sysdate));
Insert into price(id, price, price_date) Values(4, 4, trunc(sysdate));
Insert into price(id, price, price_date) Values(5, 5, trunc(sysdate));
Insert into price(id, price, price_date) Values(1, 1, trunc(sysdate)-1);
Insert into price(id, price, price_date) Values(2, 2.1, trunc(sysdate)-1);
Insert into price(id, price, price_date) Values(3, 3, trunc(sysdate)-1);
Insert into price(id, price, price_date) Values(4, 4, trunc(sysdate)-1);
Insert into price(id, price, price_date) Values(5, 5, trunc(sysdate)-1);
Insert into price(id, price, price_date) Values(1, 1, trunc(sysdate)-2);
Insert into price(id, price, price_date) Values(2, 2.1, trunc(sysdate)-2);
Insert into price(id, price, price_date) Values(3, 3.1, trunc(sysdate)-2);
Insert into price(id, price, price_date) Values(4, 4, trunc(sysdate)-2);
Insert into price(id, price, price_date) Values(5, 5, trunc(sysdate)-2);
Insert into price(id, price, price_date) Values(1, 1, trunc(sysdate)-3);
Insert into price(id, price, price_date) Values(2, 2.1, trunc(sysdate)-3);
Insert into price(id, price, price_date) Values(3, 3.1, trunc(sysdate)-3);
Insert into price(id, price, price_date) Values(4, 4.1, trunc(sysdate)-3);
Insert into price(id, price, price_date) Values(5, 5, trunc(sysdate)-3);
Insert into price(id, price, price_date) Values(1, 1, trunc(sysdate)-4);
Insert into price(id, price, price_date) Values(2, 2.1, trunc(sysdate)-4);
Insert into price(id, price, price_date) Values(3, 3, trunc(sysdate)-4);
Insert into price(id, price, price_date) Values(4, 4, trunc(sysdate)-4);
Insert into price(id, price, price_date) Values(5, 5.1, trunc(sysdate)-4);
what I need is given input date as trunc(sysdate)
id price no_of_days
1 1 5
2 2 1
3 3 2
4 4 3
5 5 4
I need to get the total number of consecutive days price has been same as today's price.
Followup June 28, 2005 - 8pm Central time zone:
ops$tkyte@ORA9IR2> select id, price, count(*)
2 from (
3 select id, price, price_date,
4 max(grp) over (partition by id order by price_date) max_grp
5 from (
6 select id, price, price_date,
7 case when nvl(lag(price_date) over (partition by id order by price_date),price_date) <>
price_date-1
8 or
9 nvl(lag(price) over (partition by id order by price_date),price-1) <> price
10 then row_number() over (partition by id order by price_date)
11 end grp
12 from price
13 )
14 )
15 group by id, price, max_grp
16 having max(price_date) = trunc(sysdate)
17 /
ID PRICE COUNT(*)
---------- ---------- ----------
1 1 5
2 2 1
3 3 2
4 4 3
5 5 4
ops$tkyte@ORA9IR2>
can be simplified in 10g with IGNORE NULLS and first/last_value()
weekend data
June 29, 2005 - 7pm Central time zone
Reviewer: Mita from NJ
how it would handle missing days in between ?
Followup June 29, 2005 - 8pm Central time zone:
lets see how you work that out - you've seen the logic, the analytics -- did you give it a try?
you have unspoken requirements here, to me consecutive means something. You made no mention of
weekends
but -- give it a go!
Fantastic
August 11, 2005 - 9am Central time zone
Reviewer: Ian from Suffolk, UK
Have been looking for how to pivot a result set and your DECODE routine works wonders.
Brilliant as always.
Cheers Tom,
Ian
Concat char column in SQL statement without user defined function
September 8, 2005 - 6am Central time zone
Reviewer: Ashutosh Upadhyay from Bangalore, India
TOM,
I have table tblx, which has 1 column called emp_name. This table may have millions of records. I
just want to fire a sql query which gives me 1 record & 1 column (offcourse) with all these values
concatenated.
Is there any wayout?
Followup September 8, 2005 - 8am Central time zone:
not with millions of records.
you will write code for this rather unique request.
Pivot
November 14, 2005 - 11am Central time zone
Reviewer: Yoav from Israel
Hy Tom.
In your book "Effective Oracle By Design" Pg. 522 you showed how to pivot.
Your example was very helpful for me.
My Problem is that i need to pivot by look-up table.
In my exaple "size_sub_group_cd" represent a size code.
My proplem is that this size code is hidden from the users.
I need to display the description for this code from look-up table.
Example:
create table s1_test -- LOOK UP TABLE
(size_group_cd number,
size_group_desc varchar2(20));
insert into s1_test values(100,'SHIRTS');
insert into s1_test values(100,'SHOES');
select * from t1_test;
SIZE_GROUP_CD SIZE_GROUP_DESC
------------- --------------------
100 SHIRTS
100 SHOES
create table s2_test -- LOOK UP TABLE
(size_group_sd number,
size_sub_group_cd number,
size_sub_desc varchar2(20));
insert into s2_test values(100,1,'S');
insert into s2_test values(100,2,'M');
insert into s2_test values(100,3,'L');
insert into s2_test values(100,4,'XL');
insert into s2_test values(100,5,'XXL');
insert into s2_test values(101,1,'36');
insert into s2_test values(101,2,'38');
insert into s2_test values(101,3,'40');
insert into s2_test values(101,4,'42');
insert into s2_test values(101,5,'44');
select * from t2_test;
SIZE_GROUP_SD SIZE_SUB_GROUP_CD SIZE_SUB_DESC
------------- ----------------- -------------
100 1 S
100 2 M
100 3 L
100 4 XL
100 5 XXL
101 1 36
101 2 38
101 3 40
101 4 42
101 5 44
create table t1_test
(
item_code varchar2(23) not null,
size_group_cd number(3),
size_sub_group_cd number,
stock_qty number
)
insert into t1_test values('ABC',100,1,2);
insert into t1_test values('ABC',100,2,0);
insert into t1_test values('ABC',100,3,1);
insert into t1_test values('ABC',100,4,3);
insert into t1_test values('ABC',100,5,2);
insert into t1_test values('XYZ',101,1,8);
insert into t1_test values('XYZ',101,2,4);
insert into t1_test values('XYZ',101,3,5);
insert into t1_test values('XYZ',101,4,0);
insert into t1_test values('XYZ',101,5,1);
commit;
SELECT item_code,
size_group_cd,
max(DECODE(size_sub_group_cd,1, stock_qty)) size1,
max(DECODE(size_sub_group_cd,2, stock_qty)) size2,
max(DECODE(size_sub_group_cd,3, stock_qty)) size3,
max(DECODE(size_sub_group_cd,4, stock_qty)) size4,
max(DECODE(size_sub_group_cd,5, stock_qty)) size5
FROM (SELECT item_code,size_group_cd,size_sub_group_cd,
sum (stock_qty) stock_qty
from t1_test
group by item_code,size_group_cd,size_sub_group_cd)
group by item_code, size_group_cd
/
ITEM_CODE SIZE_GROUP_CD SIZE1 SIZE2 SIZE3 SIZE4 SIZE5
------------ ------------- ----- ----- ----- ----- -----
ABC 100 2 0 1 3 2
XYZ 101 8 4 5 0 1
The result that i wanted is:
ITEM_CODE SIZE_GROUP_CD S M L XL XXL
------------ ------------- ----- ----- ----- ----- -----
ABC 100 2 0 1 3 2
ITEM_CODE SIZE_GROUP_CD 36 38 40 42 44
------------ ------------- ----- ----- ----- ----- -----
XYZ 101 8 4 5 0 1
Thank You Very Much.
Followup November 14, 2005 - 2pm Central time zone:
you would need two different queries as a column can have only ONE NAME. Are you *sure* you really
want to do this? You can (requires writing two queries with different column names - a query per
"type" in fact)
Just cheating
November 14, 2005 - 4pm Central time zone
Reviewer: Michel Cadot from France
SQL> select * from t2_test;
SIZE_GROUP_SD SIZE_SUB_GROUP_CD SIZE_SUB_DESC
------------- ----------------- --------------------
100 1 S
100 2 M
100 3 L
100 4 XL
100 5 XXL
101 1 36
101 2 38
101 3 40
101 4 42
101 5 44
10 rows selected.
SQL> select * from t1_test;
ITEM_CODE SIZE_GROUP_CD SIZE_SUB_GROUP_CD STOCK_QTY
----------------------- ------------- ----------------- ----------
ABC 100 1 2
ABC 100 2 0
ABC 100 3 1
ABC 100 4 3
ABC 100 5 2
XYZ 101 1 8
XYZ 101 2 4
XYZ 101 3 5
XYZ 101 4 0
XYZ 101 5 1
10 rows selected.
SQL> set heading off
SQL> col t format a24
SQL> col sg format a80
SQL> col tit format a105
SQL> col id format a10 newline
SQL> col sgcd format a13
SQL> col stocklist format a80
SQL> set recsep each
SQL> set colsep '|'
SQL> select rpad('ITEM_CODE',10)||'|SIZE_GROUP_CD' t,
2 max(substr(sys_connect_by_path(size_sub_desc,'|'),2)) sg,
3 '----------|-------------|'||
4 max(substr(sys_connect_by_path(rpad('-',length(size_sub_desc),'-'),'|'),2))
5 tit,
6 rpad(item_code,10) id,
7 lpad(to_char(size_group_cd),13,' ') sgcd,
8 max(substr(sys_connect_by_path(stock_qty,'|'),2)) stocklist
9 from ( select item_code, size_group_cd,
10 lpad(size_sub_desc,5,' ') size_sub_desc,
11 lpad(to_char(stock_qty),5,' ') stock_qty,
12 row_number ()
13 over (partition by t1_test.item_code, t1_test.size_group_cd
14 order by t1_test.size_sub_group_cd) curr,
15 row_number ()
16 over (partition by t1_test.item_code, t1_test.size_group_cd
17 order by t1_test.size_sub_group_cd) - 1 prev
18 from t1_test, t2_test
19 where t2_test.size_group_sd = t1_test.size_group_cd
20 and t2_test.size_sub_group_cd = t1_test.size_sub_group_cd )
21 connect by prior curr = prev and prior item_code = item_code and prior size_group_cd =
size_group_cd
22 start with curr = 1
23 group by item_code, size_group_cd
24 /
ITEM_CODE |SIZE_GROUP_CD| S| M| L| XL| XXL
----------|-------------|-----|-----|-----|-----|-----
ABC | 100| 2| 0| 1| 3| 2
ITEM_CODE |SIZE_GROUP_CD| 36| 38| 40| 42| 44
----------|-------------|-----|-----|-----|-----|-----
XYZ | 101| 8| 4| 5| 0| 1
2 rows selected.
Regards
Michel
Followup November 15, 2005 - 7am Central time zone:
that's a neat idea ;)
best sql resource anywhere!
December 16, 2005 - 12pm Central time zone
Reviewer: ion from nyc
thanks a lot, Tom! even for a novice as myself, your answers always throw some light on my
problems. right now i relized "decode" is just a case statement, all the mistery is gone :)
Can I pivot this query? or maybe analytical function?
February 21, 2006 - 4pm Central time zone
Reviewer: A reader
select all count (e_code ) ,error_2 , error_3 || ' -- ' ||error_simple
from table_1
where student = 0 and student_pass = 'Y'
and (code_simple = 'SING' or main_code is null)
and (main_date between to_date('03-FEB-05 00:00:00','DD-MM-RR HH24:MI:SS')
and to_date('03-FEB-06 23:59:59','DD-MM-RR HH24:MI:SS'))
and student_code in ('','TRANSFERED')
and (universal = 'SA')
group by e_code ,error_2, error_3 || ' -- ' ||error_simple
Followup February 22, 2006 - 8am Central time zone:
funny, I don't have a table_1 in my database.
Nor do I have any idea whatsoever you might want to pivot exactly..
Come on Tom - anybody can pivot that query!!!
February 23, 2006 - 11pm Central time zone
Reviewer: Silence Dogood from Philadelphia
s f w a a a a a g
e r h n n n n n r
l o e d d d d d o
e m r u
c e ( ( t s ( p
t t c m o t u
a s o a _ u n b
a b t d i d d i y
l l u e n a e v
l e d _ _ t n e e
_ e s d e t r _
c 1 n i a ( _ s c
o t m t ' c a o
u p e 0 o l d
n = l 3 d e
t e b - e =
0 e F ,
( = t E i ' e
e a w B n S r
_ n ' e - A r
c d S e 0 ( ' o
o I n 6 ' ) r
d s N ' _
e t G t 2 , 2
u ' o 3 ' ,
) d _ : T
e o d 5 R e
, n r a 9 A r
e t t : N r
r _ m e 5 S o
r p a ( 9 F r
o a i ' ' E _
r s n 0 , R 3
_ s _ 3 ' E
2 c - D D |
= o F D ' |
, d E - )
' e B M '
e Y - M
r ' i 0 - -
r s 5 R -
o R
r n 0 '
_ u 0 H
3 l : H |
l 0 2 |
| ) 0 4 e
| : : r
0 M r
' 0 I o
' : r
- , S _
- ' S s
D ' i
' D ) m
- ) p
| M l
| M e
e -
r R
r R
o
r H
_ H
s 2
i 4
m :
p M
l I
e :
S
S
'
)
Column to Row Translation
March 21, 2006 - 5am Central time zone
Reviewer: Duncan
Hi Tom
Thanks for the advice regarding the Pivot Query at the start of this question. It proved to be most
useful to me.
I have a question about translating a table the other way (i.e. coverting columns to rows)
I currently have a table COSTS defined as
CREATE TABLE costs (site VARCHAR2 (22) NOT NULL,
cost1 NUMBER,
cost2 NUMBER,
cost3 NUMBER,
cost4 NUMBER);
INSERT INTO costs (site, cost1, cost4)
VALUES ('SITE_ONE', 2000, 255);
INSERT INTO costs (site, cost2)
VALUES ('SITE_TWO', 100);
INSERT INTO costs (site, cost3, cost4)
VALUES ('SITE_THREE', 145, 5000);
COMMIT;
The table would look something like this:
SITE COST1 COST2 COST3 COST4
-------------------------------------------------------
SITE_ONE 2000 255
SITE_TWO 100
SITE_THREE 145 5000
I would like to translate it to the following:
SITE COST_ID VALUE
--------------------------------
SITE_ONE COST1 2000
SITE_ONE COST4 255
SITE_TWO COST2 100
SITE_THREE COST3 145
SITE_THREE COST4 5000
I have a query that workes using UNION ALL for all the cost columns (4 in this case) but I was
hoping there was a way to get the desired output without having to have the same query 4 times in a
UNION ALL
i.e.
SELECT site, 'COST1' cost_id, cost1
FROM costs
where cost1 IS NOT NULL
UNION ALL
SELECT site, 'COST2' cost_id, cost2
FROM costs
where cost2 IS NOT NULL
UNION ALL
SELECT site, 'COST3' cost_id, cost3
FROM costs
where cost3 IS NOT NULL
UNION ALL
SELECT site, 'COST4' cost_id, cost4
FROM costs
where cost4 IS NOT NULL;
Thanks for any suggestions
Duncan
Followup March 22, 2006 - 1pm Central time zone:
using any 4 row table (we can make one up on the fly in 9i and above as follows), you just
cartesian product and decode or case out the right column:
ops$tkyte@ORA10GR2> with data
2 as
3 (select level l from dual connect by level <= 4)
4 select site,
5 decode(l,1,cost1,2,cost2,3,cost3,4,cost4) cost
6 from data, costs
7 where decode(l,1,cost1,2,cost2,3,cost3,4,cost4) is not null
8 order by 1
9 /
SITE COST
---------------------- ----------
SITE_ONE 2000
SITE_ONE 255
SITE_THREE 5000
SITE_THREE 145
SITE_TWO 100
Excellent!!!!
March 23, 2006 - 7am Central time zone
Reviewer: Duncan
Thaks Tom.
That has greatly improved the performance of my ETL process.
Appreciate the help and advice.
Regards
WITH DATA AS and Full Outer Joins?
March 23, 2006 - 7am Central time zone
Reviewer: Duncan
Tom.
Can a query using the "WITH DATA AS" syntax highlighted in the example above, be used in a full
outer join?
The example below would suggest not.
CREATE TABLE costs2
(
site VARCHAR2(22 BYTE) NOT NULL,
cost_id NUMBER,
COST NUMBER
);
INSERT INTO COSTS2 (SITE_XXX, 1, 2000000);
COMMIT;
SELECT a.*, b.*
FROM (WITH DATA AS
(SELECT LEVEL l
FROM DUAL
CONNECT BY LEVEL <= 4)
SELECT site,
DECODE (l, 1, cost1, 2, cost2, 3, cost3, 4, cost4) COST
FROM DATA, costs
WHERE DECODE (l, 1, cost1, 2, cost2, 3, cost3, 4, cost4) IS NOT NULL) a
FULL OUTER JOIN
(SELECT site, cost_id, COST
FROM costs2) b ON a.site = b.site
Thanks
Followup March 23, 2006 - 11am Central time zone:
the with would go at the TOP - before SELECT, but yes, you can outerjoin to it.
Here is a "neat" example that shows this:
ops$tkyte@ORA9IR2> variable txt varchar2(25)
ops$tkyte@ORA9IR2> exec :txt := 'SYS,NOT_A_USER'
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> pause
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> with data
2 as
3 (
4 select substr (txt,
5 instr (txt, ',', 1, level ) + 1,
6 instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1 )
7 as token
8 from (select ','||:txt||',' txt from dual)
9 connect by level <= length(:txt)-length(replace(:txt,',',''))+1
10 )
11 select data.token, count(all_objects.owner)
12 from data, all_objects
13 where data.token = all_objects.owner(+)
14 group by data.token;
TOKEN COUNT(ALL_OBJECTS.OWNER)
---------------------------------- ------------------------
NOT_A_USER 0
SYS 13895
ops$tkyte@ORA9IR2>
the table "DATA" could be used in an in-list as well (how to bind an inlist...)
Excellent Example
March 24, 2006 - 5am Central time zone
Reviewer: Duncan Mein
Thanks Tom for that example.
It has helped me get my query running in a fraction of the time that my original FULL OUTER JOIN
example ran in.
I must admit I am still not 100 % sure of how the "WITH" syntax works and need to spend some time
reading the documentation. Is there a Chapter in your "Expert Oracle: One-On-One" or "Effective
Oracle by Design" that you could refer me to?
Thanks again
Regards
Duncan
Followup March 24, 2006 - 9am Central time zone:
I don't have anything specific on WITH (subquery factoring)
It is rather simple syntax:
with RESULT_SET_NAME_1 as (query),
RESULT_SET_NAME_2 as (query,
....
RESULT_SET_NAME_N as (query)
select ...
from (can reference result_set_name_xx here)
Response has been really helpful but need more help
March 30, 2006 - 6pm Central time zone
Reviewer: See from US
I am trying to do something similar.
I wrote a sql to create a report which looks like this
state place hour sum1 date
a 1 0 123 1/1/2006
a 2 0 353 1/1/2006
a 3 0 546 1/1/2006
a 1 1 767 1/1/2006
a 2 1 875 1/1/2006
a 3 1 674 1/1/2006
. . 2 ... .....
. . 2 ... .....
10 ... .....
10 ... .....
a 1 0 321 1/2/2006
a 2 0 543 1/2/2006
a 3 0 654 1/2/2006
a 1 1 765 1/2/2006
a 2 1 876 1/2/2006
a 3 1 987 1/2/2006
. . . ... ...
. . . ... ...
. . . ... ...
Now, I want the report to look like this:
state place hour 1/1/2006 1/2/2006 1/3/2006 1/4/2006 ... ... ...
a 1 0 123 321 ... ......
a 2 0 353 543 ... ......
a 3 0 546 654 ... ......
a 1 1 767 765 ... ......
a 2 1 875 876 ... ......
a 3 1 674 987 ... ......
.
.
.
.
.
.
. 23 ...
. 23 ...
. 23 ...
The sql I wrote to gen the first report is:
select c.state ,d.place,b.hour ,sum(nvl(b.abc,0)) ,trunc(a.datevalue)
from a,b,c,d
where a.col1 = b.col1
and b.col2 = c.col2
and c.col3 = d.col3
and c.state IN (Select State FROM StateList)
and a.datevalue between '01-JAN-2006' and '01-DEC-2006'
group by c.state,d.place,b.hour,a.datevalue
order by a.datevalue,hour;
How do I modify it to look like the expected report( 2nd report shown):
I thought I could use Decode function and get it. But in that case, I have to write 30 decode
functions in the select query.Is that wise to do? I tried it for 3 dates but I still couldnt get
it. I dont know where I am messing it up. Please help
Use some reporting tool for such reports.
April 6, 2006 - 10am Central time zone
Reviewer: A reader
this substring in decode has fixed my problem in 8.1.7
August 3, 2006 - 4pm Central time zone
Reviewer: A reader
Thanks !!
August 5, 2006 - 2pm Central time zone
Reviewer: Aps
Hi Tom!
Found an excellent solution to my problem using pivot query
as you suggested.
One small issue remains. I have 2 tables as shown below.
Table A (Pk = ID)
-------
ID
==
1
2
3
Table B (PK= BID, FK=ID of table A)
-----------------------------------
Values look like this
BID| ID| NAME| VALUE
=============================
10 | 1 | Prop1| 555
20 | 1 | Prop1| 111
30 | 1 | Prop1| 222
40 | 1 | Prop2| AAA
50 | 2 | Prop1| 123
60 | 2 | Prop2| DDD
Using pivot I have a query like below
select b.id,
max(decode(name,'Prop1', value)) ADet,
max(decode(name,'Prop2', value)) BDet
from a, b
where a.id = b.id
group by b.id
which gives me results as
ID| ADet| BDet
====================
1| 555 | AAA
2| 123 | DDD
since Prop1 has multiple values for Id = 1, what I need is the most recent value which is decided
by the BID column. Hence need 222(BID=30) in place of 555(BID=10). Is there a way to achieve this?
I have no control over the way data is inserted in Table B
CREATE TABLE A
(ID NUMBER);
INSERT INTO A VALUES (1); // and 2 and 3
CREATE TABLE B
( BID NUMBER,
ID NUMBER,
NAME VARCHAR2(20),
VALUE VARCHAR2(50)
)
INSERT INTO B vALUES (10, 1, 'Prop1', '555');
INSERT INTO B vALUES (20, 1, 'Prop1', '111');
INSERT INTO B vALUES (30, 1, 'Prop1', '333');
INSERT INTO B vALUES (40, 1, 'Prop2', 'AAA');
INSERT INTO B vALUES (50, 1, 'Prop1', '123');
INSERT INTO B vALUES (60, 1, 'Prop2', 'DDD');
Followup August 5, 2006 - 5pm Central time zone:
ops$tkyte%ORA10GR2> select a.id,
2 substr( max( decode( b.name, 'Prop1', to_char(b.bid,'fm0000000000')||value ) ),11) d1,
3 substr( max( decode( b.name, 'Prop2', to_char(b.bid,'fm0000000000')||value ) ),11) d2
4 from a, b
5 where a.id = b.id
6 group by a.id;
ID D1 D2
---------- ----- -----
1 123 DDD
make b.bid "sortable" as a string (fm0000000000 - 10 digits with leading zeros, I assume bid is a
positive integer - if more than 10 digits - you know what to do...)
max it
substr it off.
What about LOBs?
August 6, 2006 - 5am Central time zone
Reviewer: Mike Friedman from Shezhen, China
Can this be done with CLOBS and BLOBS?
Irritatingly enough, all the obvious ways of doing this like MAX, LEAD, etc. don't work on CLOBS
and BLOBS.
Followup August 6, 2006 - 9am Central time zone:
aggregation isn't going to happen there, no. Never ran into a case where I'd want to pivot LOBs
though - what is the real use case?
pivot result set without decode function
August 7, 2006 - 2am Central time zone
Reviewer: A reader
Customer@Devt> create table my_pivot_tbl ( name varchar2(100));
Table created.
Customer@Devt>
Customer@Devt> insert into my_pivot_tbl values ( 'abc' );
1 row created.
Customer@Devt>
Customer@Devt> insert into my_pivot_tbl values ( 'def' );
1 row created.
Customer@Devt>
Customer@Devt> insert into my_pivot_tbl values ( 'ghi' );
1 row created.
Customer@Devt> select name from my_pivot_tbl;
NAME
--------------------------------------------------------------------------------
abc
def
ghi
Customer@Devt>
i want pivot the result set. is it possible to do it? because most of the examples i have seen was
using the "decode" function! assume there n number of rows in the above example table.
Followup August 7, 2006 - 8am Central time zone:
yes, you still use decode (or case)
what is wrong with that?
Performance of pivot queries
August 8, 2006 - 10am Central time zone
Reviewer: Aps
Appreciate your help on the 'sortable' column solution.
My existing table has around 1.5 million rows. Wanted to create a view around this table using
pivot queries. How performance intensive will this get?
Thanks.
Followup August 9, 2006 - 9am Central time zone:
insufficient data to even begin to answer.
it generally doesn't matter how many rows are in a table.
it matters how many rows you end up processing.
Performance
August 10, 2006 - 5am Central time zone
Reviewer: A reader
Hi Tom,
I have 2 tables as shown below
Table A (Pk = ID)
-------
ID
==
1
2
3
Table B (PK= BID, FK=ID of table A)
-----------------------------------
Values look like this
BID| ID| NAME| VALUE
=============================
10 | 1 | Prop1| 555
20 | 1 | Prop1| 111
30 | 1 | Prop1| 222
40 | 1 | Prop2| AAA
50 | 2 | Prop1| 123
60 | 2 | Prop2| DDD
Using pivot I have a query like below
select b.id,
max(decode(name,'Prop1', value)) ADet,
max(decode(name,'Prop2', value)) BDet
from a, b
where a.id = b.id
group by b.id
which gives me results as
ID| ADet| BDet
====================
1| 555 | AAA
2| 123 | DDD
Table B has 1.5 million rows and I will be creating a View (say VIEW_B) around table B using the
pivot query. Cannot discard table B for now, need to use it but move away from the approach
sometime in future. Hence considering the view option.
Client code will make a JDBC call like
select * from view_b
where ID = 1
Assuming this would equate to
select * from (execute view's query)
then append the where clause
Hence concerned if this would impact performance. Table B can have around 2000 distinct ID values
contributing to the total 1.5 million rows.
Followup August 10, 2006 - 9am Central time zone:
ugh, I call this the "funky data model", hate it in almost every case. Like this one.
We can push predicates
We can merge views
http://www.oracle.com/technology/oramag/oracle/05-mar/o25asktom.html see "Views and Merging/Pushing"
It'll merge in all likelyhood
ops$tkyte%ORA10GR2> create table t1 ( id int primary key, data char(30) );
Table created.
ops$tkyte%ORA10GR2> create table t2 ( bid int primary key, id references t2, name varchar2(5),
value number );
Table created.
ops$tkyte%ORA10GR2> create index t2_idx on t2(id);
Index created.
ops$tkyte%ORA10GR2> exec dbms_stats.set_table_stats( user, 'T1', numrows => 500000 );
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> exec dbms_stats.set_table_stats( user, 'T2', numrows => 1500000 );
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace view vw
2 as
3 select b.id,
4 max(decode(name,'Prop1', value)) ADet,
5 max(decode(name,'Prop2', value)) BDet
6 from t1 a, t2 b
7 where a.id = b.id
8 group by b.id
9 /
View created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> select *
2 from vw
3 where id = 1
4 /
Execution Plan
----------------------------------------------------------
Plan hash value: 1136627723
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | C
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15000 | 629K|
| 1 | SORT GROUP BY NOSORT | | 15000 | 629K|
| 2 | NESTED LOOPS | | 15000 | 629K|
|* 3 | INDEX UNIQUE SCAN | SYS_C0013374 | 1 | 13 |
| 4 | TABLE ACCESS BY INDEX ROWID| T2 | 15000 | 439K|
|* 5 | INDEX RANGE SCAN | T2_IDX | 6000 | |
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."ID"=1)
5 - access("B"."ID"=1)
ops$tkyte%ORA10GR2> set autotrace off
August 11, 2006 - 4am Central time zone
Reviewer: Muhammad Ibrahim from Hongkong
Customer@Devt> create table my_pivot_tbl ( name varchar2(100));
Table created.
Customer@Devt>
Customer@Devt> insert into my_pivot_tbl values ( 'abc' );
1 row created.
Customer@Devt>
Customer@Devt> insert into my_pivot_tbl values ( 'def' );
1 row created.
Customer@Devt>
Customer@Devt> insert into my_pivot_tbl values ( 'ghi' );
1 row created.
Customer@Devt> select name from my_pivot_tbl;
NAME
--------------------------------------------------------------------------------
abc
def
ghi
Customer@Devt>
i want pivot the result set. is it possible to do it? because most of the
examples i have seen was using the "decode" function! assume there n number of
rows in the above example table.
Followup:
yes, you still use decode (or case)
what is wrong with that?
sorry i didnt get you. could you pls show me how to use decode or case in the above example. tom,
why i am asking because i dont have a value to equate with decode or case rather simply its 'n' no.
of rows(from single colmun) but need to show it as columns. please show me with decode or case.
Followup August 11, 2006 - 10am Central time zone:
if you want to pivot a set so as to have N columns....
YOU MUST KNOW WHAT N IS
SQL wants to have a fixed number of columns, if you don't know N, then you must:
a) count the rows (to discover N)
b) build a query with N columns
c) run that query from b
Select statement to an in list
December 12, 2006 - 4pm Central time zone
Reviewer: Raj from India
select CY_CODE
from cy_tab
where
pay_settle_hr = 'C100'
will get
abc
xyz
etc ...
is it possible to transpose the result as an in list
'xyz','abc' like wise
Looking for your help
Rajesh
Followup December 12, 2006 - 10pm Central time zone:
shown above?
Response has been really helpful but need more help March 30, 2006
December 17, 2006 - 8pm Central time zone
Reviewer: ST from MI USA
I was looking for a solution for my problem which is exactly the same as posted on March 30, 2006
as above in this post by one of the reviewers which says:
"Response has been really helpful but need more help March 30, 2006 "
Please suggest if there is any way to get the desired output. Thanks
Followup December 17, 2006 - 8pm Central time zone:
I'd love to help.
but unfortunately...
no create table
no insert into
no look
and the person that posted that on march 30th, well, they already figured it out - they knew
precisely what to do (and so would you - it is already answered in the original answer!!!!)
Response has been really helpful but need more help March 30, 2006
December 17, 2006 - 8pm Central time zone
Reviewer: ST from MI USA
In fact the result that I am looking for should look like as below
state place hour 1/1/2006 1/2/2006 1/3/2006 1/4/2006 ... ... ...
a 1 0 123 321 ... ......
a 2 0 353 543 ... ......
a 3 0 546 654 ... ......
a 1 1 767 765 ... ......
a 2 1 875 876 ... ......
a 3 1 674 987 ... ......
.
.
.
.
.
.
. 23 ...
. 23 ...
. 23 ...
Followup December 17, 2006 - 8pm Central time zone:
how many times will you ask the same thing...
but just read the original answer - IT HAS ALREADY BEEN COVERED.
Calc Diff
December 18, 2006 - 6am Central time zone
Reviewer: Yoav
Hi,
I tring to calculate the diff between the last value and the first value that exists in each row.
example:
SELECT SEG_name,
MAX( DECODE( DT,'09/2006', size_mb, null ) ) sep_size,
MAX( DECODE( DT,'10/2006', size_mb, null ) ) oct_size,
MAX( DECODE( DT,'11/2006', size_mb, null ) ) nov_size,
MAX( DECODE( DT,'12/2006', size_mb, null ) ) dec_size
FROM( SELECT OWNER||'.'||SEGMENT_NAME SEG_name, TO_CHAR(DT,'mm/yyyy') dt , SUM(BYTES)/1024/1024
SIZE_MB
FROM COLLECT_DBA_EXTENTS_SIZE
GROUP BY OWNER||'.'||SEGMENT_NAME, TO_CHAR(DT,'mm/yyyy')
)
GROUP BY seg_name
results:
SEG_NAME SEP_SIZE OCT_SIZE NOV_SIZE DEC_SIZE
======== ======== ======== ======== ========
CRM.A 3305 3370 3371 3371
CRM.B 3360 3360 0 0
CRM.C 3359 0 1 0
CRM.D 1568 1632 1664
CRM.E 1558 1560 1560
calc diff:
SEG_NAME SEP_SIZE OCT_SIZE NOV_SIZE DEC_SIZE DIFF
======== ======== ======== ======== ======== ====
CRM.A 3305 3370 3371 3371 66
CRM.B 3360 3360 0 0 -3360
CRM.C 3359 0 1 0 -3359
CRM.D 1568 1632 1664 96
CRM.E 1558 1560 1560 2
Can you please show how to calculate this column ?
Regards.
Followup December 18, 2006 - 8am Central time zone:
select ....,
coalesce( sep_size, oct_size, nov_size, dec_size ) first_one,
coalesce( dec_size, nov_size, oct_size, sep_size ) last_one
from (your query)
Tricky Unpivot in SQL no Analytics ver 9i
January 14, 2007 - 4am Central time zone
Reviewer: A reader
Hello Tom,
I have 2 tables say
ID_TAB (ID ,ID_VAL ,CNT)
Values are like
1 ,'1,2,3,4' ,4
2, '8,1,6', 3
CNT column is the Total number of elements in the ID_val column seperated by delimiter comma
I need to insert them to the following table
Using only Sql and no Analytics.
Seq column is just a running sequence staring with 1 for the first element in the Comma seperated delimited list.
Val is the every nth element from ID_VAL
ID_LIST(ID ,SEQ,VAL)
1 ,1 ,1
1, 2, 2
1 ,3, 3
1 ,4, 4
2,1, 8
2,2, 1
2,3, 6
create table ID_TAB (ID number ,ID_VAL varchar2(100) ,CNT number);
insert into id_val values (1 ,'1,2,3,4' ,4);
insert into id_val values ( 2, '8,1,6', 3 );
create table ID_LIST(ID number ,SEQ number ,VAL number).
I tried doing a cross join with a numeric table having just
sequential numbers
But I dont know how to make the query generic as There can be different number of elements for each Row. Plus I want the running Sequence also.
select id, running_seq based reset to one on each Id ,decode(r,1,get_first_val from id_val,2,get_second_val from id_val ..... ) from Id_tab
(select seq from dummy_table where r <= (select cnt id_tab
Changes Table structure from columns to rows
February 8, 2007 - 6am Central time zone
Reviewer: Neeraj Badaya from INDIA
Our table structure is changes from columns to rows e.g.
Old table structure:
Create Table T_IWFE_FEE(
Prod_id number,
st_trd_fee_a number,
st_trd_fee_prd_c number,
)
Sample Data
Prod_id st_trd_fee_a st_trd_fee_prd_c
1 35 37
Modified table Structure:
Create Table T_IWFE_FEE(
Prod_id number,
ColumnName Varchar2,
ColumnValue number,
)
Sample Data
Prod_id ColumnName ColumnValue
1 st_trd_fee_a 35
1 st_trd_fee_prd_c 37
We have old query compatible to old structure given below:
Select a.FUND_LGL_NM,a.CUSIP_X,a.CLASS_OF_SHR_C, b.st_trd_fee_a, b.st_trd_fee_prd_c
from T_IWFI_FUND_INFO a,T_IWFE_FEE b
where a.prod_id=b.prod_id;
How should we modify the above query for the columns b.st_trd_fee_a and b.st_trd_fee_prd_c as these columns are not present in the new structure. I want the same resultset as before with same column names so that i need not to change in the UI and other layers.
Please help.
Thanks in Advance.
Followup February 8, 2007 - 8am Central time zone:
please do not "modify" your good data model into something horrific.
Ordering the Columns
February 15, 2007 - 6am Central time zone
Reviewer: Elango
Hi Tom,
In the Select statement I have to get one column first, remaining columns as usual ,but need all the columns need to be displayed.
for example
Create table t(
col1 number,
col2 number,
col3 number,
col4 number
)
Output of Query must be like
col4 col1 col2 col3
Is there a way other than the select col4, col1, col2, col3 from t. Instead of specifying the column name i need to get the above result. Is it Possible.
Thanks in Advance
Elango
Followup February 15, 2007 - 11am Central time zone:
nope, you have to do it the right way - which is explicit.
explicit is good
explicit is safe
February 15, 2007 - 8pm Central time zone
Reviewer: Vinny
You can create a view that selects col4, col, col2, col3 in that order and a select * from the view.
Pivot result set
February 16, 2007 - 9am Central time zone
Reviewer: Padma from Madison, WI USA
Tom,
You and the folks here (who participates in the forum are wonderful).
The scenario's explained above are neat and crisp. But my requirement is slighty different.
Here it follows.
Table Structure as follows:
Program ID Course Name Participating Startdate EndDate
50001 Computer Science Y 10/10/2006 10/12/2006
50001 History N 1/1/2007 3/4/2007
50001 Geography N 5/5/2007 5/6/2007
50001 Biology Y 3/3/2007 3/4/2007
50002 Computer Science N 10/10/2006 10/12/2006
50002 History Y 1/1/2007 3/4/2007
50002 Geography Y 5/5/2007 5/6/2007
50002 Biology N 3/3/2007 3/4/2007
Required Output:
Program ID Course Name Participating Startdate EndDate Course Name Participating Startdate EndDate Course Name Participating Startdate EndDate Course Name Participating Startdate EndDate
50001 Computer Science Y 10/10/2006 10/12/2006 History N 1/1/2007 3/4/2007 Geography N 5/5/2007 5/6/2007 Biology Y 3/3/2007 3/4/2007
50002 Computer Science N 10/10/2006 10/12/2006 History Y 1/1/2007 3/4/2007 Geography Y 5/5/2007 5/6/2007 Biology N 3/3/2007 3/4/2007
P.S: Copy the data to excel or notepad to see in better display format.
What is want is; to create a procedure and pass "Program ID", Course Name, and one or more other columns ex: Start Date" as parameter and get the
output as above.
ProgramID Coursenames as columns, and start dates as columns.
I am sure this is doable but can't find a way to make it dynamic. I am in desparate need of your help.
Thanks
Padma
Followup February 17, 2007 - 9am Central time zone:
ps: give us a create table and insert into statements and we'll take a look at it....
Pivot Result set.
February 20, 2007 - 4pm Central time zone
Reviewer: A reader
create table course
(
student_ID varchar2(20),
course_name varchar2(20),
Participating_Flag char(1),
Start_dt date,
End_dt date
);
/
Insert into course values
(
'50001', 'Computer Science', 'Y', '10-OCT-2006', '10-DEC-2006'
)
/
Insert into course values
(
'50001', 'History', 'N', '01-Jan-2007', '03-APR-2007'
)
/
Insert into course values
(
'50001', 'Geography', 'N', '01-Jan-2007', '03-APR-2007'
)
/
Insert into course values
(
'50001', 'Biology', 'Y', '01-Jan-2007', '03-APR-2007'
)
/
Insert into course values
(
'50002', 'Computer Science', 'N', '01-Jan-2007', '03-APR-2007'
)
/
Insert into course values
(
'50002', 'History', 'Y', '01-Jan-2007', '03-APR-2007'
)
/
Insert into course values
(
'50002', 'Geography', 'Y', '01-Jan-2007', '03-APR-2007'
)
/
Insert into course values
(
'50002', 'Biology', 'N', '01-Jan-2007', '03-APR-2007'
)
/
Followup February 20, 2007 - 8pm Central time zone:
ops$tkyte%ORA9IR2> select sid, nm,
2 max( decode( rn, 1, flag ) ) f1, max( decode( rn, 1, sdt ) ) std1,
3 max( decode( rn, 2, flag ) ) f2, max( decode( rn, 2, sdt ) ) std2,
4 max( decode( rn, 3, flag ) ) f3, max( decode( rn, 3, sdt ) ) std3,
5 max( decode( rn, 4, flag ) ) f4, max( decode( rn, 4, sdt ) ) std4,
6 max( decode( rn, 5, flag ) ) f5, max( decode( rn, 5, sdt ) ) std5
7 from (
8 select student_id sid,
9 course_name nm,
10 participating_flag flag,
11 start_dt sdt,
12 row_number() over (partition by student_id order by start_dt) rn
13 from course
14 where course_name = 'Computer Science'
15 )
16 group by sid, nm
17 order by sid, nm
18 /
SID NM F STD1 F STD2 F STD3 F
-------------------- -------------------- - --------- - --------- - --------- -
STD4 F STD5
--------- - ---------
50001 Computer Science Y 10-OCT-06
50002 Computer Science N 01-JAN-07
Pivot result set.
February 21, 2007 - 8pm Central time zone
Reviewer: Padma from Madison,WI
Tom,
Thanks for your quick response, I believe i have not explained my requriement...
Let me explain and request your help.
I want to create a dynamic procedure which can accept N number of parameters(those will be column names) as input and return data per the below requested format.
Table:
create table course
( cid number, crs_name varchar2(2),
flg char(1), st_date date)
/
insert into course values
(1, 'CS', 'Y', '01-Jan-2007')
/
insert into course values
(1, 'BO', 'Y', '03-Jan-2007')
/
insert into course values
(1, 'MS', 'N', '01-Feb-2007')
/
Commit
/
The output should be:
c1 c2 c3 c4 c5 c6 c7 c8 c9 c10
____________________________________________________________
1, CS, Y, 01-Jan-2007, BO, Y, 03-Jan-2007, MS, N, 01-Feb-2007
Constraint: I will not be passing all the columns as parameter, sometimes user can pass one column or more than one column so the procedure should be dynamic to generate a output as above.
I need your help to address this need ASAP.
Thanks so so much in advance.
Thanks
Padma
Pivoting Words from a Row
April 12, 2007 - 6am Central time zone
Reviewer: Jignesh from London
Tom,
We have a requirement where we need to store all 'valid' unique words from a particular column of original table to a separate table. The original table has got more than 2 million records.
Following is the sampe code :
create table T_ORG_NAMES (organisation_search_name varchar2(200))
/
set define off
Insert into T_ORG_NAMES (organisation_search_name) Values ('BARKING FORGE');
Insert into T_ORG_NAMES (organisation_search_name) Values ('CHINNOR BATHROOM');
Insert into T_ORG_NAMES (organisation_search_name) Values ('HADLEIGH ENTERPRISE');
Insert into T_ORG_NAMES (organisation_search_name) Values ('PHONES4U');
Insert into T_ORG_NAMES (organisation_search_name) Values ('CLOUD FIVE BEAUTY CLINIC');
Insert into T_ORG_NAMES (organisation_search_name) Values ('OLD BUCKENHAM HALL SCHOOL');
Insert into T_ORG_NAMES (organisation_search_name) Values ('MAR & SPENS');
Insert into T_ORG_NAMES (organisation_search_name) Values ('FIVE OAK DESIGN');
COMMIT;
-- Since we need unique words, I am creating index organized table
CREATE TABLE TMP_ORG_INDEXES
(
ORG_SEARCH_NAME VARCHAR2(100) PRIMARY KEY
)
ORGANIZATION INDEX
/
DECLARE
cursor c_split_hdr is
select * from (
select
case when
length(trim(translate(upper(organisation_search_name),'ABCDEFGHIJKLMNOPQRSTUVWXYZ&.',' '))) is null then
organisation_search_name
else NULL
end organisation_search_name
from T_ORG_NAMES)
where organisation_search_name is not null;
BEGIN
for r_split_hdr in c_split_hdr loop
declare
p_org_name varchar2(200) := replace(r_split_hdr.organisation_search_name,' ','|')||'|';
p_length number := length(p_org_name)-length(replace(p_org_name,'|',''));
p_split_name varchar2(100);
begin
for i in 1..p_length loop
p_split_name := substr(p_org_name,1,instr(p_org_name,'|',1));
p_org_name := replace(p_org_name,p_split_name,null);
BEGIN
insert into tmp_org_indexes values (replace(p_split_name,'|',null));
EXCEPTION
when dup_val_on_index then null;
END;
end loop;
end;
if mod(c_split_hdr%rowcount,1000) = 0 then
commit;
end if;
end loop;
commit;
END;
/
It works fine if the above code is executed for few records. However, if I use above code with original table (with more than 2 million rows), it takes lots of time. Is there any better way of writing above code? How can we tune above query?
Thanks
Jignesh
can we acheive this?
March 20, 2008 - 12pm Central time zone
Reviewer: gsreddy from NJ
CREATE TABLE PRODUCTS(PRODUCT VARCHAR2(100) NOT NULL, CATGORY_ID VARCHAR2(15), CREATE_DT DATE, RATE
NUMBER(22,3));
INSERT INTO PRODUCTS(PRODUCT, CATGORY_ID, CREATE_DT, RATE) VALUES('HA2006786208', 'HMAPP',
To_Date('05-01-2007','DD-MM-RRRR'),999.99);
INSERT INTO PRODUCTS(PRODUCT, CATGORY_ID, CREATE_DT, RATE) VALUES('HA2006724335', 'HMAPP',
To_Date('03-08-2007','DD-MM-RRRR'),1999.95);
INSERT INTO PRODUCTS(PRODUCT, CATGORY_ID, CREATE_DT, RATE) VALUES('HA2006760881', 'HMAPP',
To_Date('08-10-2007','DD-MM-RRRR'),999.99);
INSERT INTO PRODUCTS(PRODUCT, CATGORY_ID, CREATE_DT, RATE) VALUES('HA2006864081', 'HMAPP',
To_Date('05-01-2007','DD-MM-RRRR'),3999.99);
INSERT INTO PRODUCTS(PRODUCT, CATGORY_ID, CREATE_DT, RATE) VALUES('HA2005645673', 'HMAPP',
To_Date('04-07-2007','DD-MM-RRRR'),999.99);
INSERT INTO PRODUCTS(PRODUCT, CATGORY_ID, CREATE_DT, RATE) VALUES('ELC200787266', 'ELAPP',
To_Date('25-03-2007','DD-MM-RRRR'),999.99);
INSERT INTO PRODUCTS(PRODUCT, CATGORY_ID, CREATE_DT, RATE) VALUES('HA2006786081', 'HMAPP',
To_Date('05-02-2007','DD-MM-RRRR'),599.99);
INSERT INTO PRODUCTS(PRODUCT, CATGORY_ID, CREATE_DT, RATE) VALUES('ELC200787266', 'ELAPP',
To_Date('15-05-2007','DD-MM-RRRR'),3999.99);
INSERT INTO PRODUCTS(PRODUCT, CATGORY_ID, CREATE_DT, RATE) VALUES('FRN200086356', 'HMAPP',
To_Date('05-02-2007','DD-MM-RRRR'),299.99);
INSERT INTO PRODUCTS(PRODUCT, CATGORY_ID, CREATE_DT, RATE) VALUES('HA0067457465', 'HMAPP',
To_Date('02-06-2007','DD-MM-RRRR'),399.99);
INSERT INTO PRODUCTS(PRODUCT, CATGORY_ID, CREATE_DT, RATE) VALUES('HA2006789701', 'HMAPP',
To_Date('13-11-2007','DD-MM-RRRR'),999.99);
INSERT INTO PRODUCTS(PRODUCT, CATGORY_ID, CREATE_DT, RATE) VALUES('HA2006786081', 'HMAPP',
To_Date('05-01-2007','DD-MM-RRRR'),799.99);
INSERT INTO PRODUCTS(PRODUCT, CATGORY_ID, CREATE_DT, RATE) VALUES('ELC200787266', 'ELAPP',
To_Date('25-05-2007','DD-MM-RRRR'),249.99);
INSERT INTO PRODUCTS(PRODUCT, CATGORY_ID, CREATE_DT, RATE) VALUES('HA2006786081', 'HMAPP',
To_Date('05-01-2007','DD-MM-RRRR'),945.99);
INSERT INTO PRODUCTS(PRODUCT, CATGORY_ID, CREATE_DT, RATE) VALUES('HA2006786081', 'HMAPP',
To_Date('05-12-2007','DD-MM-RRRR'),999.99);
INSERT INTO PRODUCTS(PRODUCT, CATGORY_ID, CREATE_DT, RATE) VALUES('ELC200787266', 'ELAPP',
To_Date('15-12-2007','DD-MM-RRRR'),9143.99);
INSERT INTO PRODUCTS(PRODUCT, CATGORY_ID, CREATE_DT, RATE) VALUES('HAN20086F356', 'HMAPP',
To_Date('05-12-2007','DD-MM-RRRR'),86.99);
INSERT INTO PRODUCTS(PRODUCT, CATGORY_ID, CREATE_DT, RATE) VALUES('HA2006757465', 'HMAPP',
To_Date('02-06-2007','DD-MM-RRRR'),5639.99);
INSERT INTO PRODUCTS(PRODUCT, CATGORY_ID, CREATE_DT, RATE) VALUES('HA2006786081', 'HMAPP',
To_Date('16-07-2007','DD-MM-RRRR'),999.99);
INSERT INTO PRODUCTS(PRODUCT, CATGORY_ID, CREATE_DT, RATE) VALUES('HA2006786081', 'HMAPP',
To_Date('17-09-2007','DD-MM-RRRR'),956.99);
INSERT INTO PRODUCTS(PRODUCT, CATGORY_ID, CREATE_DT, RATE) VALUES('ELC200787266', 'ELAPP',
To_Date('05-01-2007','DD-MM-RRRR'),958.99);
INSERT INTO PRODUCTS(PRODUCT, CATGORY_ID, CREATE_DT, RATE) VALUES('HA2006786081', 'HMAPP',
To_Date('25-03-2007','DD-MM-RRRR'),789.99);
INSERT INTO PRODUCTS(PRODUCT, CATGORY_ID, CREATE_DT, RATE) VALUES('ELC200787266', 'HMAPP',
To_Date('06-01-2007','DD-MM-RRRR'),323.99);
INSERT INTO PRODUCTS(PRODUCT, CATGORY_ID, CREATE_DT, RATE) VALUES('HA2006786081', 'HMAPP',
To_Date('05-03-2007','DD-MM-RRRR'),99.99);
INSERT INTO PRODUCTS(PRODUCT, CATGORY_ID, CREATE_DT, RATE) VALUES('ELC200787266', 'ELAPP',
To_Date('05-01-2007','DD-MM-RRRR'),912.99);
INSERT INTO PRODUCTS(PRODUCT, CATGORY_ID, CREATE_DT, RATE) VALUES('HA2006786081', 'HMAPP',
To_Date('14-05-2007','DD-MM-RRRR'),1234.14);
INSERT INTO PRODUCTS(PRODUCT, CATGORY_ID, CREATE_DT, RATE) VALUES('HA2006789886', 'HMAPP',
To_Date('24-04-2007','DD-MM-RRRR'),49.99);
SELECT Product,
SUM(DECODE(TO_CHAR(create_dt,'MM'),'01',1,0)) Jan,
SUM(DECODE(TO_CHAR(create_dt,'MM'),'02',1,0)) Feb,
SUM(DECODE(TO_CHAR(create_dt,'MM'),'03',1,0)) Mar,
SUM(DECODE(TO_CHAR(create_dt,'MM'),'04',1,0)) Apr,
SUM(DECODE(TO_CHAR(create_dt,'MM'),'05',1,0)) May,
SUM(DECODE(TO_CHAR(create_dt,'MM'),'06',1,0)) Jun,
SUM(DECODE(TO_CHAR(create_dt,'MM'),'07',1,0)) Jul,
SUM(DECODE(TO_CHAR(create_dt,'MM'),'08',1,0)) Aug,
SUM(DECODE(TO_CHAR(create_dt,'MM'),'09',1,0)) Sep,
SUM(DECODE(TO_CHAR(create_dt,'MM'),'10',1,0)) Oct,
SUM(DECODE(TO_CHAR(create_dt,'MM'),'11',1,0)) Nov,
SUM(DECODE(TO_CHAR(create_dt,'MM'),'12',1,0)) Dec
FROM products
GROUP BY product;
which produces the following output
PRODUCT JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
HA2006864081 1 0 0 0 0 0 0 0 0 0 0 0
HA2006757465 0 0 0 0 0 1 0 0 0 0 0 0
ELC200787266 3 0 1 0 2 0 0 0 0 0 0 1
HA2006786081 2 1 2 0 1 0 1 0 1 0 0 1
FRN200086356 0 1 0 0 0 0 0 0 0 0 0 0
HA2006789886 0 0 0 1 0 0 0 0 0 0 0 0
HA2006760881 0 0 0 0 0 0 0 0 0 1 0 0
HA2006789701 0 0 0 0 0 0 0 0 0 0 1 0
HA2006724335 0 0 0 0 0 0 0 1 0 0 0 0
HAN20086F356 0 0 0 0 0 0 0 0 0 0 0 1
HA2005645673 0 0 0 0 0 0 1 0 0 0 0 0
HA0067457465 0 0 0 0 0 1 0 0 0 0 0 0
HA2006786208 1 0 0 0 0 0 0 0 0 0 0 0
how can i achieve vice versa result set
here products can be n number
Followup March 24, 2008 - 10am Central time zone:
if by "vice versa" you mean product numbers become columns and your columns become rows - you will need to two step it - you need the unique set of product numbers (just like you have for the set of months) and a column per product number - sql needs to know the number of columns at parse time, you need to tell it
How to Create Dynamic Column Names?
April 21, 2008 - 8pm Central time zone
Reviewer: Gabor Revesz from Toronto, Ontario, Canada
In all the examples I have seen, the column names of the result seem to be hard coded in the query.
Please let me know how to create the column names dynamically.
Example:
create table instructor(id number, name varchar2(30));
insert into instructor values(1,'ALPHA');
insert into instructor values(2,'BRAVO');
insert into instructor values(3,'CHARLIE');
create table student(id number, name varchar2(30));
insert into student values(1,'X-RAY');
insert into student values(2,'YANKEE');
insert into student values(3,'ZULU');
--Pair up instructors and students for an hour:
create table schedule(id number,iid number,sid number,start_time date);
insert into schedule values(1,1,1,to_date('17','HH24'));
insert into schedule values(2,1,2,to_date('18','HH24'));
insert into schedule values(3,1,1,to_date('19','HH24'));
insert into schedule values(4,2,2,to_date('17','HH24'));
insert into schedule values(5,2,3,to_date('19','HH24'));
insert into schedule values(6,3,3,to_date('17','HH24'));
insert into schedule values(7,3,3,to_date('18','HH24'));
commit;
The desired result of the query (which I don't want to rewrite if new
instructors/students/schedules are added):
TIME ALPHA BRAVO CHARLIE
------- ------- ------- -------
17 X-RAY YANKEE ZULU
18 YANKEE ZULU
19 X-RAY ZULU
Thanks!
Gabor
Followup April 23, 2008 - 5pm Central time zone:
... Please let me know how to create the column names
dynamically.
...
you cannot - they have to be known at parse time.
How to Create Dynamic Column Names?
April 24, 2008 - 8am Central time zone
Reviewer: Gabor Revesz from Toronto, Ontario, Canada
Thanks...
Gabor
Pivoting 3 columns into 2
April 30, 2008 - 2am Central time zone
Reviewer: Shubhasree from India
Hi Tom,
SELECT sscl.subx_set_db_id "Client lvl subx",
sssfk.subx_set_db_id "Sfk lvl subx", client.client_db_id
FROM (SELECT subx_set_db_id, ss.subx_set_level
FROM subx_set ss
WHERE ss.subx_set_level = 'CLIENT' AND ss.deleted = 0
AND ss.closed = 0) sscl,
(SELECT subx_set_db_id, ss.subx_set_level
FROM subx_set ss
WHERE ss.subx_set_level = 'SFKACC' AND ss.deleted = 0
AND ss.closed = 0) sssfk,
subx_set_client ssc,
subx_set_sfk_acc sssa,
client
WHERE sssfk.subx_set_db_id = sssa.subx_set_sfk_acc_db_id
AND sscl.subx_set_db_id = ssc.subx_set_client_db_id
AND ssc.client_db_id = client.client_db_id
AND sssa.subx_set_client_db_id = ssc.subx_set_client_db_id;
The above query fetches result as:
Client lvl subx Sfk lvl subx CLIENT_DB_ID
10000013163 10000013164 10000002380
10000013163 10000013165 10000002380
10000013163 10000013166 10000002380
10000013163 10000013167 10000002380
10000013163 10000013168 10000002380
I Want this to be like:
subx CLIENT_DB_ID
10000013163 10000002380
10000013164 10000002380
10000013165 10000002380
10000013166 10000002380
10000013167 10000002380
10000013168 10000002380
The results as above are used in another query as inline view. The desired result is the composite key of subx and Client_db_id only whereas the Client lvl subx and SFk lvl subx are both subx (sharing the same relation as Manager and employee) but in the same table subx_set. Also, the table design cannot be altered to make the situation handleable using CONNECT BY PRIOR.
I've tried a couple of tricks with row_number etc but can't get what i want.
Please could you suggest me something?
(I'm not supposed to use UNION, UNION ALL and WITH as far as possible)
Regards,
Shubhasree
Followup April 30, 2008 - 10am Central time zone:
... (I'm not supposed to use UNION, UNION ALL and WITH as far as possible) ...
I cannot tell you how much that statement annoys me. Seriously, what brain dead sort of rule is that?
You cannot do that, but you can write a query like the one above?
but since you give me no create table, no inserts, NO RELATIONAL INFORMATION (like 1:1, 1:m, "this inline view returns a single row" and so on).... I cannot really do anything for you.
May 29, 2008 - 5pm Central time zone
Reviewer: Reader
create table tst
(id varchar2(2)
,q1 number
,q2 number
,q3 number
,q4 number);
truncate table tst;
INSERT INTO tst VALUES( 'A', 100,200,300,400);
INSERT INTO tst VALUES( 'B', 500,600,700,800);
commit;
SQL> select * from tst;
ID Q1 Q2 Q3 Q4
-- ---------- ---------- ---------- ----------
A 100 200 300 400
B 500 600 700 800
I want the result to be:
Q ID val
Q1 A 100
Q1 B 500
Q2 A 200
Q2 B 600
Q3 A 300
Q3 B 700
Q4 A 400
Q4 B 800
Can you please tell me how to do this?
Followup May 29, 2008 - 9pm Central time zone:
ops$tkyte%ORA11GR1> select q, id, val
2 from tst
3 unpivot ( val for Q in ( Q1, Q2, Q3, Q4 ) )
4 order by 1, 2
5 /
Q ID VAL
-- -- ----------
Q1 A 100
Q1 B 500
Q2 A 200
Q2 B 600
Q3 A 300
Q3 B 700
Q4 A 400
Q4 B 800
8 rows selected.
but that requires 11g, which probably just bothered you to read - like not seeing a version does for me :)
so, here is a 9i and up way....
if you are before 9i, you can see the approach, you need a table with at least 4 rows, use that...
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> with data as (select level l from dual connect by level <= 4)
2 select decode( l, 1, 'Q1', 2, 'Q2', 3, 'Q3', 4, 'Q4' ) q,
3 id,
4 decode( l, 1, Q1, 2, Q2, 3, Q3, 4, Q4 ) val
5 from tst, data
6 order by 1, 2
7 /
Q ID VAL
-- -- ----------
Q1 A 100
Q1 B 500
Q2 A 200
Q2 B 600
Q3 A 300
Q3 B 700
Q4 A 400
Q4 B 800
8 rows selected.
May 30, 2008 - 9am Central time zone
Reviewer: Reader
Thanks for your response.
I have read about pivot/unpivot in 11g. We are still in 10g.
Thanks a lot again.
suggestion
June 11, 2008 - 2pm Central time zone
Reviewer: db2_oracle_dba from Redmond,WA
A little query that will display result set of one column as a row with values listed side by side.
With Xtbl as ( Select SYS_CONNECT_BY_PATH(empno,' ') X_empno
from ( select empno, rownum as Y
from ( select distinct empno
from emp_table
$WhereClause
order by empno
)
)
START WITH Y >= 1
connect by Y = prior Y + 1
order by Length(X_empno) DESC
)
Select X_empno from Xtbl where rownum = 1
For example :
EmpNo
-------
356
478
901
254
37
Will be displayed as
X_empno
---------------------------------------
37 254 356 478 901
September 10, 2009 - 6pm Central time zone
Reviewer: Reader
create table country
(c_id varchar2(20)
,country_1 varchar2(20)
,country_2 varchar2(20)
,country_3 varchar2(20)
,country_4 varchar2(20)
);
insert into country (c_id,country_1,country_2) values ('R123','USA','CAN');
insert into country (c_id,country_2,country_3) values ('R223','LN','SWI');
insert into country (c_id,country_1,country_4) values ('R323','AUS','FRA');
insert into country (c_id,country_1,country_2,country_3,country_4) values
('R423','RUS','GBR','IN','GER');
commit;
select * from country;
C_ID COUNTRY_1 COUNTRY_2 COUNTRY_3 COUNTRY_4
-------------------- -------------------- -------------------- --------------------
--------------------
R123 USA CAN
R223 LN SWI
R323 AUS FRA
R423 RUS GBR IN GER
Can you please let me know how to produce data as show below?
C_ID Country
----------------
R123 USA
R123 CAN
R223 LN
R223 SWI
R323 AUS
R323 FRA
R423 RUS
R423 GBR
R423 IN
R423 GER
Followup September 14, 2009 - 11am Central time zone:
ops$tkyte%ORA11GR1> with data
2 as
3 (select level l from dual connect by level <= 4)
4 select c_id, decode(l,1,country_1,2,country_2,3,country_3,4,country_4) cty, l
5 from country, data
6 where decode(l,1,country_1,2,country_2,3,country_3,4,country_4) is not null
7 /
C_ID CTY L
-------------------- -------------------- ----------
R123 USA 1
R323 AUS 1
R423 RUS 1
R123 CAN 2
R223 LN 2
R423 GBR 2
R223 SWI 3
R423 IN 3
R323 FRA 4
R423 GER 4
10 rows selected.
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select c_id, cty, thing
2 from country unpivot ( cty for thing in (country_1, country_2, country_3, country_4 ))
3 /
C_ID CTY THING
-------------------- -------------------- ---------
R123 USA COUNTRY_1
R123 CAN COUNTRY_2
R223 LN COUNTRY_2
R223 SWI COUNTRY_3
R323 AUS COUNTRY_1
R323 FRA COUNTRY_4
R423 RUS COUNTRY_1
R423 GBR COUNTRY_2
R423 IN COUNTRY_3
R423 GER COUNTRY_4
10 rows selected.
September 11, 2009 - 11am Central time zone
Reviewer: Reader
Tom,
Using on of the examples in your posting I did as below -
Not sure if this is the right way to do. Oracle version is 10g R2. I cannot use pivot/unpivot
functions.
Please let me know if there is any other better way to do this.
select *
from(
with data
as
(select 1 r from dual union all select 2 r from dual union all select 3 r from dual union all
select 4 r from dual)
select c_id,
decode( r, 1, country_1, 2, country_2, 3, country_3,country_4 ) ctry
from country, data
order by 1,2)
where acc is not null;
Followup September 14, 2009 - 12pm Central time zone:
move the order by to the outer layer if you want the data sorted.
Pivot table
September 29, 2009 - 1am Central time zone
Reviewer: Manu from India
Hi Tom,
I have 3 tables which are a 1 - M relationship and user who want a horizontal view of the data.
create table emp (ename varchar2(100),empid number)
insert into emp (ename,empid) values ('Rakesh',1)
insert into emp (ename,empid) values ('John',2)
insert into emp (ename,empid) values ('Mohan',3)
create table field(fname varchar2(50), fid number)
insert into field (fname,fid) values ('X',1)
insert into field (fname,fid) values ('XII',2)
insert into field (fname,fid) values ('BSC',3)
insert into field (fname,fid) values ('BTech',4)
insert into field (fname,fid) values ('MTech',5)
create table data (empid number,fid number,data number)
insert into data (empid,fid,data) values (1,1,50)
insert into data (empid,fid,data) values (1,4,70)
insert into data (empid,fid,data) values (1,5,30)
insert into data (empid,fid,data) values (2,3,40)
insert into data (empid,fid,data) values (2,2,80)
insert into data (empid,fid,data) values (2,1,90)
insert into data (empid,fid,data) values (3,1,40)
insert into data (empid,fid,data) values (3,2,80)
insert into data (empid,fid,data) values (3,5,70)
How can i get values like this ==>
ename X XII BSC BTech MTech
Rakesh 50 70 30
John 90 80 70
Mohan 40 80 70
Regards,
Manu
Followup October 2, 2009 - 8am Central time zone:
read the first answer, that is what it does.
Pivot table and Static Column name
December 2, 2009 - 5pm Central time zone
Reviewer: Walter from Atlanta, GA USA
Excellent exchange of ideas here.
I am on 8i, and I am trying to figure out a way if the pivot concept will work in my situation.
We have multiple Agreements (10,000) and customers (5,000), but I wanted to group the Active and
Planned agreements, without the replication of the Qtr data (much bigger column set in real world)
create table budget (cust varchar2(10), Qtr1 number(7,2), Qtr2 number(7,2), Qtr3 number(7,2), Qtr4
number(7,2))
insert into budget values('K12577',1000.00, 1000.00, 1000.00, 1000.00)
insert into budget values('K12578',2000.00, 2000.00, 2000.00, 2000.00)
insert into budget values('K12579',3000.00, 3000.00, 3000.00, 3000.00)
create table agree (Agreeid varchar2(10), cust varchar2(10), Startdt date, enddt date, state
varchar2(10))
insert into agree values('K12577-1','K12577', '01-01-09', '01-01-10' , 'Active')
insert into agree values('K12577-2','K12577', '01-01-10', '01-01-11' , 'Planned')
insert into agree values('K12578-1','K12578', '01-01-09', '01-01-10' , 'Active')
insert into agree values('K12578-2','K12578', '01-01-10', '01-01-11' , 'Planned')
insert into agree values('K12579-12','K12579', '01-01-09', '01-01-10' , 'Active')
select * from budget b, agree a
where b.cust = a.cust
Result set: (sorry if format is bad)
CUST QTR1 QTR2 QTR3 QTR4 AGREEID STARTDT ENDDT STATE
K12577 1000.00 1000.00 1000.00 1000.00 K12577-1 1/1/2009 1/1/2010 Active
K12577 1000.00 1000.00 1000.00 1000.00 K12577-2 1/1/2010 1/1/2011 Planned
K12578 2000.00 2000.00 2000.00 2000.00 K12578-1 1/1/2009 1/1/2010 Active
K12578 2000.00 2000.00 2000.00 2000.00 K12578-2 1/1/2010 1/1/2011 Planned
K12579 3000.00 3000.00 3000.00 3000.00 K12579-12 1/1/2009 1/1/2010 Active
What I would like to create, if possible:
CUST QTR1 QTR2 QTR3 QTR4 AGREEIDact STARTDTact ENDDTact AGREEIDpln
STARTDTpln ENDDTpln
K12577 1000.00 1000.00 1000.00 1000.00 K12577-1 1/1/2009 1/1/2010 K12577-2
1/1/2010 1/1/2011
K12579 3000.00 3000.00 3000.00 3000.00 K12579-12 1/1/2009 1/1/2010 Null
Null Null
I wanted to create a column for agreements in the two states (Active or Planned) and accompanying
dates. As the desired sample shows - I would like to see cases where there is no planned or no
active states.
Most of the examples I have seen create columns using DECODE when you know a row value - I do not
want to try and create columns based on these values, but maybe use STATE to determine how data
appears.
Followup December 4, 2009 - 2pm Central time zone:
... I wanted to create a column for agreements in the two states (Active or
Planned) and accompanying dates. As the desired sample shows - I would like to
see cases where there is no planned or no active states.
...
How did you know for customer k12577, that k12577-1 was the right one (just because of active?)
will there be more than two records for a given customer during an overlapping period of time?
will there be overlaps to deal with?
you said "As the desired sample shows - I would like to
see cases where there is no planned or no active states."
but both output records have BOTH a planned and active states. and the one that doesn't have one is missing.
you asked for "whree there is no planned OR no active states" - seems you are asking for the one record not present in the output?
Sorry for the vague questions
December 4, 2009 - 5pm Central time zone
Reviewer: Walter from Atlanta, GA USA
How did you know for customer k12577, that k12577-1 was the right one (just because of active?)
--Yes, K12577-1 had a State of ACTIVE, so I would want it to go into the AGREEIDact bucket, and the
date/price fields for the ACTIVE agreement to go into xxxAct buckets.
will there be more than two records for a given customer during an overlapping period of time?
--There could be one Active and one Planned agreement for a customer, but there will not be two
Active or Two Planned agreements.
will there be overlaps to deal with?
--The query will choose to exclude records outside the years in question.
you said "As the desired sample shows - I would like to
see cases where there is no planned or no active states."
but both output records have BOTH a planned and active states. and the one that doesn't have one is
missing.
--I was not sure when I pivot, and if the one record (K12579) only has an Active record -
would/could the result show a 0 for Planned.
--For a case where the result is 2 records that have identical budget data, but have differing
Agreement data - could I pivot or perform sub query to populate the Active & Planned fields.
you asked for "where there is no planned OR no active states" - seems you are asking for the one
record not present in the output?
--Yes, I did not know if there is type of "if Active not found , then AGREEIDAct = 0/null/blank and
AGREEIDPln would have the value"
Followup December 4, 2009 - 5pm Central time zone:
ops$tkyte%ORA10GR2> select b.cust, b.qtr1, b.qtr2, b.qtr3, b.qtr4,
2 max(case when a.state = 'Active' then a.agreeid end) agreedact,
3 max(case when a.state = 'Active' then a.startdt end) startact,
4 max(case when a.state = 'Active' then a.enddt end) endact,
5 max(case when a.state = 'Planned' then a.agreeid end) agreedpln,
6 max(case when a.state = 'Planned' then a.startdt end) startpln,
7 max(case when a.state = 'Planned' then a.enddt end) endpln
8 from budget b, agree a
9 where b.cust = a.cust
10 group by b.cust, b.qtr1, b.qtr2, b.qtr3, b.qtr4
11 order by b.cust
12 /
CUST QTR1 QTR2 QTR3 QTR4 AGREEDACT STARTACT ENDACT AGREEDPLN STARTPLN ENDPLN
------ ----- ----- ----- ----- ---------- -------- -------- ---------- -------- --------
K12577 1000 1000 1000 1000 K12577-1 01-01-09 01-01-10 K12577-2 01-01-10 01-01-11
K12578 2000 2000 2000 2000 K12578-1 01-01-09 01-01-10 K12578-2 01-01-10 01-01-11
K12579 3000 3000 3000 3000 K12579-12 01-01-09 01-01-10
Thank you so much - that did the trick!!
December 9, 2009 - 7am Central time zone
Reviewer: Walter from Atlanta, GA USA
This will do exactly what I want. Thank you for teaching me a tool I can use again and again.
December 10, 2009 - 8am Central time zone
Reviewer: Pointers from US
Hi Tom,
Thanks for your help all the time.
I have a problem, could you pls help me to find a solution for this.
I have a table say 'A'
create table a (id varchar2(10),date_time date);
insert into a values('A',to_date('12/1/2009','mm/dd/yyyy'));
insert into a values('A',to_date('12/2/2009','mm/dd/yyyy'));
insert into a values('A',to_date('12/4/2009','mm/dd/yyyy'));
insert into a values('A',to_date('12/6/2009','mm/dd/yyyy'));
insert into a values('A',to_date('12/8/2009','mm/dd/yyyy'));
insert into a values('A',to_date('12/10/2009','mm/dd/yyyy'));
insert into a values('B',to_date('12/1/2009','mm/dd/yyyy'));
insert into a values('B',to_date('12/20/2009','mm/dd/yyyy'));
as you see the table 'a' has data for the dates 1 dec,2 dec ,4 dec ,6 dec and 10 of december, 2009
for the id 'A'.
and 1,20 dec 2009 for the id 'B'
I have to generate the following output.
I have to take the whole month of december and should display flag (Y|N) for the each date in
december by considering the table 'a' has data for a 'id' column.
out put is like below.
id dec1 dec2 dec3 dec4 dec5 dec6..... ....dec31
A Y Y N Y N Y.... .... N
B Y N N N N N... .... N
above, id, dec1,dec2...dec 31 are the columns.
Please help me on this.
Regards,
Pointers
Followup December 10, 2009 - 3pm Central time zone:
select id,
max( decode( date_time, to_date('12/1/2009','mm/dd/yyyy'), 'Y', 'N' )) d1,
...
max( decode( date_time, to_date('12/31/2009','mm/dd/yyyy'), 'Y', 'N' )) d31,
from a
group by id;
December 11, 2009 - 6am Central time zone
Reviewer: Pointers from US
Hi Tom,
Thanks for the reply.
The above worked for me. But do i need to use 'n' decodes if i want to go for 'n' columns. Is there
a way that we can do it dynamically.
Thanks Tom
Regards,
Pointers.
Followup December 11, 2009 - 8am Central time zone:
a sql query has a fixed number of columns - fixed at compile time.
If you want to do it dynamically, you'll be using dynamic sql to generate the query.
ops$tkyte%ORA11GR2> create or replace procedure get_results( theMonth in date, refcur in out
sys_refcursor )
2 as
3 startDate date := trunc( theMonth, 'mm' );
4 endDate date := trunc( add_months(theMonth,1),'mm' )-1;
5 query long := 'select id';
6 begin
7 for i in 1 .. (endDate-startDate+1)
8 loop
9 query := query || q'|, max( decode( date_time, to_date( '|' ||
10 to_char(startDate+i-1,'yyyymmdd') ||
11 q'|', 'yyyymmdd' ), 'Y', 'N' )) d|' || i;
12 end loop;
13 query := query || ' from a where date_time >= :s and date_time < (:e+1) group by id order
by id ';
14 open refcur for query using startDate, endDate;
15 end;
16 /
Procedure created.
ops$tkyte%ORA11GR2> show errors
No errors.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> column d1 format a2
ops$tkyte%ORA11GR2> column d31 format a3
ops$tkyte%ORA11GR2> variable x refcursor
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> exec get_results( to_date( '01-dec-2009', 'dd-mon-yyyy' ), :x )
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> print x
ID D1 D D D D D D D D D D D D D D D D D D D D D D D D D D D D D D31
---------- -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - ---
A Y Y N Y N Y N Y N Y N N N N N N N N N N N N N N N N N N N N N
B Y N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N
|