Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Kelly .

Asked: May 02, 2000 - 1:21 pm UTC

Answered by: Tom Kyte - Last updated: September 18, 2015 - 3:24 am UTC

Category: - Version:

Viewed 10K+ times! This question is

You Asked

Hi Tom,
I have two tables which are a 1 - M relationship.
I have a user who want a horizontal view of the
data.

For instance,
Table A
Key-1 char(3);
table A values
_______
1
2
3


Table B
bkey-a char(3);
bcode char(1);
table b values
1 T
1 A
1 G
2 A
2 T
2 L
3 A

What he wants to see is
all matches between the tables
where the code
is "T" "A" or "L":
1, T, A
2, A, T, L
3, A

I have tried joining the tables to themselves
and doing an outer join but I end up
with multiple rows.
1, T
1, A
2 a
2 t
2 l
3 a
etc

Is this possible?



and we said...



Sure, using decode to transpose columns like this is fairly straightforward. I'll do it in 2 steps so you can see how it is done.

We start by joining A to B and creating a 'sparse' matrix. We'll then "squash out" the redundant rows giving us the desired effect.

ops$tkyte@8i> select key_1,
2 decode( bcode, 'T', bcode, null ) t_code,
3 decode( bcode, 'A', bcode, null ) a_code,
4 decode( bcode, 'L', bcode, null ) l_code
5 from a, b
6 where a.key_1 = b.bkey_a
7 /

KEY T A L
--- - - -
1 T
1 A
1
2 A
2 T
2 L
3 A

7 rows selected.


So, there is our 'sparse' matrix. What we want to do now is collapse the rows by key_1. Thats what group by does for us:


ops$tkyte@8i>
ops$tkyte@8i> select key_1,
2 max(decode( bcode, 'T', bcode, null )) t_code,
3 max(decode( bcode, 'A', bcode, null )) a_code,
4 max(decode( bcode, 'L', bcode, null )) l_code
5 from a, b
6 where a.key_1 = b.bkey_a
7 group by key_1
8 /

KEY T A L
--- - - -
1 T A
2 T A L
3 A


You could add a where clause in the event you have a row in A, such there no rows in B have the value T, A, L. EG:

ops$tkyte@8i> insert into a values ( '4' );
ops$tkyte@8i> insert into b values ( '4', 'Z' );

ops$tkyte@8i> select key_1,
2 max(decode( bcode, 'T', bcode, null )) t_code,
3 max(decode( bcode, 'A', bcode, null )) a_code,
4 max(decode( bcode, 'L', bcode, null )) l_code
5 from a, b
6 where a.key_1 = b.bkey_a
7 group by key_1
8 /

KEY T A L
--- - - -
1 T A
2 T A L
3 A
4 <<<<<====== you might not want that row (maybe you do?)

ops$tkyte@8i> select key_1,
2 max(decode( bcode, 'T', bcode, null )) t_code,
3 max(decode( bcode, 'A', bcode, null )) a_code,
4 max(decode( bcode, 'L', bcode, null )) l_code
5 from a, b
6 where a.key_1 = b.bkey_a
7 and b.bcode in ( 'T', 'A', 'L' ) <<<<<====== that'll get rid of it.
8 group by key_1
9 /

KEY T A L
--- - - -
1 T A
2 T A L
3 A




and you rated our response

  (111 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

April 04, 2001 - 8:36 am UTC

Reviewer: Vishaka from India


Excellent

July 06, 2001 - 6:19 am UTC

Reviewer: Andy from Kuala Lumpur, Malaysia

Turning a table on its side couldn't be easier all of a sudden!

Good Tip

November 01, 2001 - 6:16 am UTC

Reviewer: Gupta Gaurav Sharan from Pune, India

It is really worth to learn this trick.

pivot a result set

January 20, 2002 - 5:40 pm UTC

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 - 2:45 pm UTC

Reviewer: A reader


Cross Tab

April 05, 2002 - 4:02 am UTC

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

Tom Kyte

Followup  

April 05, 2002 - 9:21 am UTC

select code, name, MAX( decode ... ), MAX( decode.... )
from tmp_values
group by code, name



runtime value

April 23, 2002 - 10:01 am UTC

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 ?


Tom Kyte

Followup  

April 23, 2002 - 10:59 am UTC

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 - 12:04 pm UTC

Reviewer: reader

Thanks, Tom

I have your book. I will try from there !!


can we wrap it

June 10, 2002 - 3:32 pm UTC

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.







Tom Kyte

Followup  

June 11, 2002 - 10:19 am UTC

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 06, 2003 - 9:02 pm UTC

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 - 8:47 am UTC

Reviewer: Luca from Italy

Your explanation on pivot drive me to the solution on problem in a view.

Transpose..

July 23, 2003 - 9:44 am UTC

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..

Tom Kyte

Followup  

July 23, 2003 - 10:00 am UTC

don't understand the data here.

Using Pivot

July 23, 2003 - 10:52 am UTC

Reviewer: Amalorpavanathan from INDIA

Hi Tom,
It is very useful for me.

July 24, 2003 - 12:58 am UTC

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 - 3:52 am UTC

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

Tom Kyte

Followup  

August 11, 2003 - 7:38 am UTC

version?

Single column into Row

August 11, 2003 - 8:34 am UTC

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

Tom Kyte

Followup  

August 11, 2003 - 9:55 am UTC

search this site for

stragg



version

August 11, 2003 - 8:54 am UTC

Reviewer: vj from in

the version is 9ir2 tom.

Rgds

Tom Kyte

Followup  

August 11, 2003 - 9:59 am UTC

you too should search for

stragg



pivot

March 23, 2004 - 5:42 pm UTC

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 

Tom Kyte

Followup  

March 24, 2004 - 8:19 am UTC

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 - 10:50 pm UTC

Reviewer: A reader from Not INDIA

Excellent - this is exactly what I have been looking for. Thanks!!

YAY!

November 10, 2004 - 4:44 am UTC

Reviewer: A reader


pivot a result set

May 17, 2005 - 3:41 pm UTC

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




Tom Kyte

Followup  

May 17, 2005 - 4:18 pm UTC

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 - 10:00 am UTC

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.


Tom Kyte

Followup  

May 18, 2005 - 11:17 am UTC

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 - 5:16 pm UTC

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 - 1:40 am UTC

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??


Tom Kyte

Followup  

May 23, 2005 - 8:53 am UTC

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 - 12:20 pm UTC

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?

Tom Kyte

Followup  

May 23, 2005 - 3:51 pm UTC

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 - 4:28 am UTC

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!

Tom Kyte

Followup  

May 24, 2005 - 8:01 am UTC

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 - 8:31 am UTC

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!

Tom Kyte

Followup  

May 24, 2005 - 10:19 am UTC

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 02, 2005 - 12:31 pm UTC

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 - 6:44 pm UTC

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.


Tom Kyte

Followup  

June 28, 2005 - 8:36 pm UTC

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 - 7:12 pm UTC

Reviewer: Mita from NJ

how it would handle missing days in between ?

Tom Kyte

Followup  

June 29, 2005 - 8:25 pm UTC

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 - 9:38 am UTC

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 08, 2005 - 6:56 am UTC

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?

Tom Kyte

Followup  

September 08, 2005 - 8:47 am UTC

not with millions of records.

you will write code for this rather unique request.

Pivot

November 14, 2005 - 11:11 am UTC

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.


Tom Kyte

Followup  

November 14, 2005 - 2:06 pm UTC

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 - 4:00 pm UTC

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 

Tom Kyte

Followup  

November 15, 2005 - 7:21 am UTC

that's a neat idea ;)

best sql resource anywhere!

December 16, 2005 - 12:24 pm UTC

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 - 4:10 pm UTC

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

Tom Kyte

Followup  

February 22, 2006 - 8:24 am UTC

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 - 11:59 pm UTC

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 - 5:33 am UTC

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



Tom Kyte

Followup  

March 22, 2006 - 1:44 pm UTC

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 - 7:02 am UTC

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 - 7:29 am UTC

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



Tom Kyte

Followup  

March 23, 2006 - 11:02 am UTC

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 - 5:37 am UTC

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

Tom Kyte

Followup  

March 24, 2006 - 9:53 am UTC

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 - 6:56 pm UTC

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 06, 2006 - 10:41 am UTC

Reviewer: A reader


this substring in decode has fixed my problem in 8.1.7

August 03, 2006 - 4:26 pm UTC

Reviewer: A reader

Thanks !!

August 05, 2006 - 2:24 pm UTC

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');

Tom Kyte

Followup  

August 05, 2006 - 5:14 pm UTC

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 06, 2006 - 5:08 am UTC

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.

Tom Kyte

Followup  

August 06, 2006 - 9:14 am UTC

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 07, 2006 - 2:23 am UTC

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.



Tom Kyte

Followup  

August 07, 2006 - 8:01 am UTC

yes, you still use decode (or case)

what is wrong with that?

Performance of pivot queries

August 08, 2006 - 10:39 am UTC

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.

Tom Kyte

Followup  

August 09, 2006 - 9:50 am UTC

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 - 5:47 am UTC

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.



Tom Kyte

Followup  

August 10, 2006 - 9:30 am UTC

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 - 4:22 am UTC

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.

Tom Kyte

Followup  

August 11, 2006 - 10:48 am UTC

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 - 4:28 pm UTC

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

Tom Kyte

Followup  

December 12, 2006 - 10:08 pm UTC

shown above?

Response has been really helpful but need more help March 30, 2006

December 17, 2006 - 8:36 pm UTC

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


Tom Kyte

Followup  

December 17, 2006 - 8:49 pm UTC

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 - 8:48 pm UTC

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 ...

Tom Kyte

Followup  

December 17, 2006 - 8:51 pm UTC

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 - 6:31 am UTC

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.


Tom Kyte

Followup  

December 18, 2006 - 8:32 am UTC

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 - 4:19 am UTC

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 08, 2007 - 6:39 am UTC

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.

Tom Kyte

Followup  

February 08, 2007 - 8:31 am UTC

please do not "modify" your good data model into something horrific.



Ordering the Columns

February 15, 2007 - 6:49 am UTC

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



Tom Kyte

Followup  

February 15, 2007 - 11:57 am UTC

nope, you have to do it the right way - which is explicit.

explicit is good
explicit is safe

February 15, 2007 - 8:46 pm UTC

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 - 9:22 am UTC

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
Tom Kyte

Followup  

February 17, 2007 - 9:04 am UTC

ps: give us a create table and insert into statements and we'll take a look at it....

Pivot Result set.

February 20, 2007 - 4:13 pm UTC

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'
)
/

Tom Kyte

Followup  

February 20, 2007 - 8:45 pm UTC

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 - 8:10 pm UTC

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
Tom Kyte

Followup  

February 22, 2007 - 8:35 am UTC

see
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:766825833740#14642820392307
for an approach you can use.

Pivoting Words from a Row

April 12, 2007 - 6:34 am UTC

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 - 12:57 pm UTC

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
Tom Kyte

Followup  

March 24, 2008 - 10:27 am UTC

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 - 8:17 pm UTC

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
Tom Kyte

Followup  

April 23, 2008 - 5:31 pm UTC

... 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 - 8:30 am UTC

Reviewer: Gabor Revesz from Toronto, Ontario, Canada

Thanks...

Gabor

Pivoting 3 columns into 2

April 30, 2008 - 2:29 am UTC

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
Tom Kyte

Followup  

April 30, 2008 - 10:31 am UTC

... (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 - 5:05 pm UTC

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?




Tom Kyte

Followup  

May 29, 2008 - 9:54 pm UTC

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 - 9:09 am UTC

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 - 2:36 pm UTC

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 - 6:17 pm UTC

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
Tom Kyte

Followup  

September 14, 2009 - 11:50 am UTC

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 - 11:06 am UTC

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;
Tom Kyte

Followup  

September 14, 2009 - 12:05 pm UTC

move the order by to the outer layer if you want the data sorted.

Pivot table

September 29, 2009 - 1:48 am UTC

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
Tom Kyte

Followup  

October 02, 2009 - 8:19 am UTC

read the first answer, that is what it does.

Pivot table and Static Column name

December 02, 2009 - 5:19 pm UTC

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.
Tom Kyte

Followup  

December 04, 2009 - 2:27 pm UTC

... 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 04, 2009 - 5:06 pm UTC

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"
Tom Kyte

Followup  

December 04, 2009 - 5:47 pm UTC

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 09, 2009 - 7:46 am UTC

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 - 8:15 am UTC

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



Tom Kyte

Followup  

December 10, 2009 - 3:15 pm UTC

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 - 6:37 am UTC

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.
Tom Kyte

Followup  

December 11, 2009 - 8:04 am UTC

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



March 08, 2010 - 6:59 am UTC

Reviewer: A reader

I am trying to get result set pivot, then number of departments will be get at run time from table. How i can bind variable and some proper way to achieve it.

CREATE OR REPLACE PROCEDURE pivot_data (op_rs OUT sys_refcursor)
IS
v_dept VARCHAR2 (100);
v_query VARCHAR2 (1000);
BEGIN
SELECT LISTAGG(DEPTNO,',') WITHIN GROUP (ORDER BY DEPTNO) INTO V_DEPT FROM DEPT;

v_query :=
'SELECT *
FROM (
SELECT EMPNO,JOB,DEPTNO,SAL
FROM EMP)
PIVOT(SUM(SAL) FOR DEPTNO IN('||v_dept||'))';
OPEN op_rs FOR v_query;
END;
Tom Kyte

Followup  

March 09, 2010 - 11:27 am UTC

you cannot bind there, you have to use concatenation as you are.

just be VERY AWARE of sql injection. with numbers - you are OK, with dates and strings, you have to be very very very very careful to make sure the data you select is safe.

Getting Null Values while pivoting

May 03, 2010 - 7:13 am UTC

Reviewer: Pradeep from India

Hi Tom,
After checking this post i transposed my Rows into columns but I am getting my output as

Col 1----------Col 2----------Col 3----------Col 4----------Col 5----------Col 6
1--------------USR1-------------USR2
2--------------NULL--------------NULL---------MAT1----------MAT2
3--------------NULL--------------NULL----------NULL----------NULL---------1011

If i have 2 columns in first row, second row starts with the third column and the first two columns are null. It should start from the first column.
Tom Kyte

Followup  

May 06, 2010 - 11:46 am UTC

no create
no inserts
no look


Unpivot

May 18, 2010 - 1:38 pm UTC

Reviewer: Ganesh from Chennai, India

Hi Tom, below is the record in table

A C_1 C_2 C_3
------------------------------
60012409 5.25 4.40 3.55
34455366 3.40 1.10 3.45

I wish to get output like

A B
-------------------------------
60012409 C_1:5.25 C_2:4.40 C_3:3.55
34455366 C_1:3.40 C_2:1.10 C_3:3.45

We are using oracle 10g.

Kindly advice

Thanks
Tom Kyte

Followup  

May 24, 2010 - 9:31 am UTC

how is that an "unpivot"

how is that not just "concatenate"

select a, 'C_1:' || c_1, 'C_2:' || c_2, 'C_3:' || c_3 b from t;

Unpivot

May 25, 2010 - 1:29 pm UTC

Reviewer: Ganesh from Chennai, India

Thanks for the reply Tom.

But I need column B to have values as column_name:value pair and only those columns which has NOT NULL value should be in resultset (like in first case where C_2 is NULL)

A C_1 C_2 C_3
-------------------------------------------
60012409 5.25 3.55
34455366 3.40 1.10 3.45

A B
-------------------------------------------
60012409 C_1:5.25 C_3:3.55
34455366 C_1:3.40 C_2:1.10 C_3:3.45
Tom Kyte

Followup  

May 25, 2010 - 5:43 pm UTC

and you cannot figure that out?

select a, 
case when c_1 is not null then 'c_1:' || c_1 end ||
case when c_2 is not null then ......
 end b
from t;



simple string manipulation - very basic sort of stuff?

Unpivot

May 25, 2010 - 4:59 pm UTC

Reviewer: Ganesh from Chennai, India

Addding to the above query, the output should have value of B column as

"column1:value column2:value ....."

It should be seperated by space and not by comma

same problem i have using model clause

May 28, 2010 - 6:54 am UTC

Reviewer: ghazanfar from pakistan

table data is as
EMPNO DATEIN HOURS DY DY1 DY3 WEAK

517 4/1/2010 8.58 thu 4 1 W1
517 4/2/2010 8.58 fri 5 2 W1
517 4/3/2010 0 sat 6 3 W1
517 4/4/2010 0 sun 7 4 W1
517 4/5/2010 8.58 mon 1 5 W1
517 4/6/2010 8.58 tue 2 6 W1
517 4/7/2010 9.13 wed 3 7 W1
517 4/8/2010 9.03 thu 4 1 W2
517 4/9/2010 9.01 fri 5 2 W2
517 4/10/2010 0 sat 6 3 W2
517 4/11/2010 0 sun 7 4 W2
517 4/12/2010 9.15 mon 1 5 W2
517 4/13/2010 9.07 tue 2 6 W2
517 4/15/2010 9.01 thu 4 1 W3
517 4/16/2010 9 fri 5 2 W3
517 4/17/2010 0 sat 6 3 W3
517 4/18/2010 0 sun 7 4 W3
517 4/19/2010 9.09 mon 1 5 W3
517 4/20/2010 8.58 tue 2 6 W3
517 4/21/2010 9 wed 3 7 W3
517 4/22/2010 9.06 thu 4 1 W4
517 4/23/2010 9.03 fri 5 2 W4
517 4/24/2010 0 sat 6 3 W4
517 4/25/2010 0 sun 7 4 W4
517 4/26/2010 9.04 mon 1 5 W4
517 4/27/2010 9.03 tue 2 6 W4
517 4/28/2010 9.01 wed 3 7 W4
517 4/29/2010 9.07 thu 4 8 W4
517 4/30/2010 8.55 fri 5 9 W4

now if i write query as
select
weak,
empno,
mon,
tue,
wed,
thu,
fri,
sat,
sun
from test_model
model
return updated rows
partition by (weak, empno)
dimension by ( dy3 )
measures (hours,0 thu,0 fri,0 sat,0 sun,0 mon,0 tue,0 wed)
RULES upsert
(
thu [0] = hours [1]
, fri [0] = hours [2]
, sat [0] = hours [3]
, sun [0] = hours [4]
, mon [0] = hours [5]
, tue [0] = hours [6]
, wed [0] = hours [7]
)
/
then result appears as i desire but i want dimension on dy1 column


Pivot in 11gR2

October 01, 2010 - 2:53 am UTC

Reviewer: Rajeshwaran, Jeyabal

Tom:

Just benchmarked the PIVOT feature in 11g with old CASE approach (prior to 11g) and findings below. Table 'T' is copy of all_objects.

What was really interesting in these tests was that the cost of the two statements was equivalent except that the execution plan for the case method used a hash group by operation and the pivot plan used the new sort group by pivot operation.

Is there is any performance improvement in using PIVOT rather than using old CASE statement approach?

********************************************************************************

select * from (
select owner,object_type,object_id
from t
)
pivot
(
    count(object_id)
    for object_type in ('PROCEDURE','DESTINATION','LIBRARY','TRIGGER','JAVA SOURCE','SEQUENCE','TABLE PARTITION','INDEX PARTITION','JOB CLASS',
        'PROGRAM','EVALUATION CONTEXT','JAVA DATA','INDEX','OPERATOR','TYPE','CONTEXT','RULE SET','JAVA CLASS','EDITION','SYNONYM',
        'PACKAGE BODY','WINDOW','RULE','DIRECTORY','TABLE','PACKAGE','VIEW','TYPE BODY','SCHEDULE','JAVA RESOURCE','XML SCHEMA',
        'MATERIALIZED VIEW','CLUSTER','FUNCTION','CONSUMER GROUP','JOB','SCHEDULER GROUP','INDEXTYPE')
)
order by owner

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      3.73      16.26      65159      65170          0          30
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      3.73      16.26      65159      65170          0          30

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91 

Rows     Row Source Operation
-------  ---------------------------------------------------
     30  VIEW  (cr=65170 pr=65159 pw=0 time=0 us cost=18054 size=412377 card=807)
     30   TRANSPOSE  (cr=65170 pr=65159 pw=0 time=0 us)
    236    SORT GROUP BY PIVOT (cr=65170 pr=65159 pw=0 time=117 us cost=18054 size=12105 card=807)
4577600     TABLE ACCESS FULL T (cr=65170 pr=65159 pw=0 time=21653452 us cost=17827 size=68664000 card=4577600)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  direct path read                              506        0.24         11.91
  asynch descriptor resize                        1        0.00          0.00
  SQL*Net message from client                     2        0.01          0.01
********************************************************************************
select owner,
       max(case when object_type = 'PROCEDURE' then cnt else null end) as PROCEDURE,
       max(case when object_type = 'DESTINATION' then cnt else null end) as DESTINATION,
       max(case when object_type = 'LIBRARY' then cnt else null end) as LIBRARY,
       max(case when object_type = 'TRIGGER' then cnt else null end) as TRIGGER_cnt,
       max(case when object_type = 'JAVA SOURCE' then cnt else null end) as JAVA_SOURCE,
       max(case when object_type = 'SEQUENCE' then cnt else null end) as SEQUENC_cntE,
       max(case when object_type = 'TABLE PARTITION' then cnt else null end) as TABLE_PARTITION,
       max(case when object_type = 'INDEX PARTITION' then cnt else null end) as INDEX_PARTITION,
       max(case when object_type = 'JOB CLASS' then cnt else null end) as JOB_CLASS,
       max(case when object_type = 'PROGRAM' then cnt else null end) as PROGRAM_cnt,
       max(case when object_type = 'EVALUATION CONTEXT' then cnt else null end) as EVALUATION_CONTEXT,
       max(case when object_type = 'JAVA DATA' then cnt else null end) as JAVA_DATA,
       max(case when object_type = 'INDEX' then cnt else null end) as INDEX_cnt,
       max(case when object_type = 'OPERATOR' then cnt else null end) as OPERATOR_cnt,
       max(case when object_type = 'TYPE' then cnt else null end) as TYPE,
       max(case when object_type = 'CONTEXT' then cnt else null end) as CONTEXT_cnt,
       max(case when object_type = 'RULE SET' then cnt else null end) as RULE_SET,
       max(case when object_type = 'JAVA CLASS' then cnt else null end) as JAVA_CLASS,
       max(case when object_type = 'EDITION' then cnt else null end) as EDITION,
       max(case when object_type = 'PACKAGE BODY' then cnt else null end) as PACKAGE_BODY,
       max(case when object_type = 'WINDOW' then cnt else null end) as WINDOW,
       max(case when object_type = 'RULE' then cnt else null end) as RULE,
       max(case when object_type = 'DIRECTORY' then cnt else null end) as DIRECTORY_cnt,
       max(case when object_type = 'TABLE' then cnt else null end) as TABLE_cnt,
       max(case when object_type = 'PACKAGE' then cnt else null end) as PACKAGE_cnt,
       max(case when object_type = 'VIEW' then cnt else null end) as VIEW_cnt,
       max(case when object_type = 'TYPE BODY' then cnt else null end) as TYPE_BODY,
       max(case when object_type = 'SCHEDULE' then cnt else null end) as SCHEDULE,
       max(case when object_type = 'JAVA RESOURCE' then cnt else null end) as JAVA_RESOURCE,
       max(case when object_type = 'XML SCHEMA' then cnt else null end) as XML_SCHEMA,
       max(case when object_type = 'MATERIALIZED VIEW' then cnt else null end) as MATERIALIZED_VIEW,
       max(case when object_type = 'CLUSTER' then cnt else null end) as CLUSTER_cnt,
       max(case when object_type = 'FUNCTION' then cnt else null end) as FUNCTION_cnt,
       max(case when object_type = 'CONSUMER GROUP' then cnt else null end) as CONSUMER_GROUP,
       max(case when object_type = 'JOB' then cnt else null end) as JOB_cnt,
       max(case when object_type = 'SCHEDULER GROUP' then cnt else null end) as SCHEDULER_GROUP,
       max(case when object_type = 'INDEXTYPE' then cnt else null end) as INDEXTYPE_cnt
from ( select owner,object_type,count(*)  as cnt
        from t
        group by owner,object_type)
group by owner
order by owner

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.04          0          0          0           0
Fetch        2      2.07      16.47      65159      65170          0          30
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      2.07      16.52      65159      65170          0          30

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91 

Rows     Row Source Operation
-------  ---------------------------------------------------
     30  SORT GROUP BY (cr=65170 pr=65159 pw=0 time=0 us cost=18054 size=900 card=30)
    236   VIEW  (cr=65170 pr=65159 pw=0 time=352 us cost=18054 size=24210 card=807)
    236    HASH GROUP BY (cr=65170 pr=65159 pw=0 time=235 us cost=18054 size=12105 card=807)
4577600     TABLE ACCESS FULL T (cr=65170 pr=65159 pw=0 time=22080070 us cost=17827 size=68664000 card=4577600)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       3        0.00          0.00
  SQL*Net message from client                     3        0.00          0.00
  direct path read                              505        0.39         13.76
  asynch descriptor resize                        2        0.00          0.00
******************************************************************************** 

Tom Kyte

Followup  

October 01, 2010 - 6:17 am UTC

they have to do more or less the same thing - the advantage of pivot is the syntax and the fact it is "standard" more than anything.

OK

July 04, 2011 - 8:34 am UTC

Reviewer: Kumar from Pune,India

Hi Tom,
Please see my req. below

create table department(dname varchar2(30),sal number)
/
insert into department values('Accounting',5000)
/
insert into department values('Marketing',2500)
/
insert into department values('Sales',2500)
/--
insert into department values('Finance',5000)
/
commit
/


Data comes as

dname sal
------- ------
Accouting 5000
Marketing 2500
Sales 7000
Finance 5000

I want output as

Accounting Marketing Sales Finance
5000 2500 7000 5000

Is there a way we can dynamically transpose row as column.
Column values cannot be hardcoded anc checked as values may change at anytime.
Can you give some idea to solve this?

Tom Kyte

Followup  

July 05, 2011 - 11:46 am UTC

you will have to run a query to get the set of values, then dynamically build a query to return that output using the set of values you just retrieved. that dynamic query will either use max(decode()) or PIVOT in 11g.

ops$tkyte%ORA11GR2> create table department(dname varchar2(30),sal number)
  2  /

Table created.

ops$tkyte%ORA11GR2> insert into department values('Accounting',5000)
  2  /

1 row created.

ops$tkyte%ORA11GR2> insert into department values('Marketing',2500)
  2  /

1 row created.

ops$tkyte%ORA11GR2> insert into department values('Sales',2500)
  2  /

1 row created.

ops$tkyte%ORA11GR2> insert into department values('Finance',5000)
  2  /

1 row created.

ops$tkyte%ORA11GR2> commit
  2  /

Commit complete.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace procedure pivot_table( p_cursor in out sys_refcursor )
  2  as
  3      l_query long := 'select ';
  4      l_sep   varchar2(10);
  5  begin
  6      for x in (select distinct dname from department order by 1)
  7      loop
  8          l_query := l_query || l_sep ||
  9                     'max(decode(dname,'''||x.dname||''', sal )) "' ||
 10                     x.dname || '"';
 11          l_sep := ' , ';
 12      end loop;
 13      l_query := l_query || ' from department';
 14      open p_cursor for l_query;
 15  end;
 16  /

Procedure created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> variable x refcursor
ops$tkyte%ORA11GR2> exec pivot_table( :x )

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> print x

Accounting    Finance  Marketing      Sales
---------- ---------- ---------- ----------
      5000       5000       2500       2500


Fine

July 08, 2011 - 7:31 am UTC

Reviewer: Kumar from Pune,India

Hi Tom,
Thank you.

In my original requirement, column types will be as follows
dname - varchar2
sal - CLOB

With the solution provided, I get the error as
ORA-00932: inconsistent datatypes: expected - got CLOB

I converted the dname as a clob using dbms_lob.substr() function. But still I get the error.

I need the

Any way to correct this?

Thanks for your time Tom.
Tom Kyte

Followup  

July 08, 2011 - 3:19 pm UTC

SAL would have had to have dbms_lob.substr applied to it, not DNAME.

but, even so, it won't work with a clob. It just isn't going to work.

ok

July 10, 2011 - 6:22 am UTC

Reviewer: Kumar from Pune,India

Hi Tom,
Any other way to fix this?
Thank you.
Tom Kyte

Followup  

July 12, 2011 - 7:20 am UTC

it will not work with lobs, no.

Simple pivot when number of rows <=1000

July 10, 2011 - 5:35 pm UTC

Reviewer: Alex

About 5 months ago I was asked to produce a report from the table created by abc user (average webpage response time by page and date). In production we have limited options, so here is the quick solution to let other people use the result as they pleased.
Please, use your skills to modify the code for your needs.

when number of rows <= 1000

Source table: src_tbl
col1 col2 col3
date_ page avg_tt


alter table abc.src_tbl parallel 4;
exec dbms_stats.gather_table_stats('abc','src_tbl',estimate_percent=>100,degree=>4);

create index abc.src_table#main on src_tbl(page, date_, avg_tt);

declare
cr varchar2(4) := chr(10)||', ';
l_sql long := 'create table pivot_rpt('||cr||' page varchar2(128)';
begin
for r in (select distinct date_ d from abc.src order by 1
) loop
l_sql := l_sql||cr||'"'||to_char(r.d,'yyyy-mm-dd')||'" number';
end loop;
l_sql := l_sql||cr||')';
execute immediate l_sql;
--dbms_output.put_line(l_sql);
end;
/
--desc pivot_rpt;
grant select on pivot_rpt to abc;

declare
l_row pivot_rpt%rowtype;
begin
for r in (select distinct page from abc.src order by 1
) loop
l_row.page := r.page;
insert into pivot_rpt values l_row;
end loop;
commit;
end;
/

declare
l_str varchar2(64):= 'update pivot_rpt set x=y where rowid=''z''';
l_sql varchar2(256);
begin
for r in (
select rowid rid, page from pivot_rpt
) loop
for d in (
select date_, '"'||to_char(date_,'yyyy-mm-dd')||'"' dt, avg_tt
from abc.src
where page=r.page
) loop
l_sql := replace(l_str, 'x', d.dt);
l_sql := replace(l_sql, 'y', d.avg_tt);
l_sql := replace(l_sql, 'z', r.rid);
execute immediate l_sql;
--dbms_output.put_line(l_sql);
end loop;
end loop;
commit;
end;
/

exec dbms_stats.gather_table_stats(user,'pivot_rpt',estimate_percent=>100,degree=>4);

Correction to the previous review

July 10, 2011 - 5:48 pm UTC

Reviewer: Alex from USA

use

declare
cr varchar2(4) := chr(10);
l_sql long := 'create table pivot_rpt('||cr||' page varchar2(128)';
begin
for r in (select distinct date_ d from abc.src order by 1
) loop
l_sql := l_sql||cr||', "'||to_char(r.d,'yyyy-mm-dd')||'" number';
end loop;
l_sql := l_sql||cr||')';
execute immediate l_sql;
--dbms_output.put_line(l_sql);
end;
/

pivot + left-shift

September 08, 2011 - 6:54 pm UTC

Reviewer: James from Portland, OR

Hello Tom.

For your consideration:

create table t
(s_id number,
id1 number,
id2 number,
id3 number,
id4 number);

insert into t values (1,10,null,null,null);
insert into t values (1,21,null,null,null);
insert into t values (1,32,null,null,null);
insert into t values (2,null,44,null,null);
insert into t values (3,null,null,56,null);
insert into t values (3,null,null,61,null);
insert into t values (4,null,null,null,72);
commit;

What I need to do is collapse S_IDs down to single rows and pivot & left-shift the IDn's to obtain this layout:

S_ID ID1 ID2 ID3 ID4
---- --- --- --- ---
1 10 21 32
2 44
3 56 61
4 72

I've tried various case statements and max decodes, but nothing quite works. I know it's probably something rrreally obvious, but can't quite grasp it.
Can you help me please?
Tom Kyte

Followup  

September 08, 2011 - 7:00 pm UTC

what happens if there is more than 4 non-null values? or is there a rule that says "there shall only be one id column that is not-null and only four rows at most" - that is left "unstated"??

(if so, why the heck are there four columns if only one can be not null at a time??? )

pivot + left-shift: followup

September 08, 2011 - 8:17 pm UTC

Reviewer: James from Portland, OR

Hello Tom.

Thanks for the clarifying questions:
1) An S_ID *must have* one or more IDn's.
2) Yes, there can be more than four IDn's (I was hoping to build upon a nifty "here's an approach" logic to include additional IDs as they may occur and I figured 4 IDs would be a decent starting point). The most I've seen is 15 IDn's for a given S_ID.
3) It's similar to the original question in that I have a sparse matrix that needs to be collapsed, but it's that 'left shifting' concept (found in S_IDs 2 and 3) that adds the twist.

Hope that makes sense for you. Thanks again!
- James
Tom Kyte

Followup  

September 09, 2011 - 7:00 am UTC

ops$tkyte%ORA11GR2> with data
  2  as
  3  (select level l
  4     from dual
  5  connect by level <= 4)
  6  select s_id,
  7         max(decode(rn,1,id)) id_1,
  8         max(decode(rn,2,id)) id_2,
  9         max(decode(rn,3,id)) id_3,
 10         max(decode(rn,4,id)) id_4
 11    from (
 12  select s_id, decode( l, 1, id1, 2, id2, 3, id3, 4, id4 ) id,
 13         row_number() over (partition by s_id order by decode( l, 1, id1, 2, id2, 3, id3, 4, id4 ) ) rn
 14    from t, data
 15   where decode( l, 1, id1, 2, id2, 3, id3, 4, id4 ) is not null
 16         )
 17   group by s_id
 18   order by s_id
 19  /

      S_ID       ID_1       ID_2       ID_3       ID_4
---------- ---------- ---------- ---------- ----------
         1         10         21         32
         2         44
         3         56         61
         4         72




add more max(decodes) for more columns...

on "pivot + left-shift"

September 09, 2011 - 9:58 am UTC

Reviewer: Stew Ashton from Paris, France


Tom's answer works on many versions of Oracle. I wanted to see what PIVOT/UNPIVOT could do even though they only work on 11G.
with data as (
  select s_id, id, row_number() over(partition by s_id order by id) rn
  from t
  unpivot (id for col in (id1, id2, id3, id4))
)
select * from data
pivot(max(id) for rn in (1 as id1, 2 as id2, 3 as id3, 4 as id4, 5 as overflow_1))
order by s_id;
I am impressed by how concise PIVOT and UNPIVOT can be. Letting us do analytics on the column produced by the UNPIVOT clause is cool.
Tom Kyte

Followup  

September 09, 2011 - 11:46 am UTC

Thanks Stew - I was thinking of using pivot/unpivot myself, but decided in light of the "lack of version information", I'd go with the one that works in 9i and above...

pivot + left-shift: followup-2

September 09, 2011 - 10:29 am UTC

Reviewer: James from Portland, OR

Hi Tom.

Okay, so to get a full learning experience here - I see that 'LEVEL' is needed in order to provide a framework for the DECODEs, which eliminate the nulls. The implied join between T and DATA is on T.S_ID = DATA.L, correct? Then the row_number() function uses the same decode in its 'order by'. This gets the RN per S_ID. From there, it's a standard 'max decode' pivot using the RNs. Other than a nice and clean way to select four rows from dual (sure beats doing a bunch of 'select # from dual' unions), is there any significance to the 'connect by level' clause?

Many thanks!
- James
Tom Kyte

Followup  

September 09, 2011 - 12:00 pm UTC

there is no implied join, it is a cartesian join, on purpose.


We take every row in T and make four rows out of it, each of those four rows gets assigned a row_number() after the filter about NULL takes place.

and row_number() is used to pivot the data back :)

pivot + left-shift: followup-3

September 09, 2011 - 10:33 am UTC

Reviewer: James from Portland, OR

Merci, Mssr. Ashton!

That's a totally cool feature. Very clean. Thanks for pointing that out.
- James

rows to cols doesn't fit in

September 15, 2011 - 5:52 am UTC

Reviewer: Umesh from INDIA

CREATE TABLE TestCase
(
OrdNo VARCHAR2(10),
BU Varchar2(50),
ETA date,
OrdQty Number,
OrdWk Number
)


INSERT INTO TestCase VALUES('Ord5', 'CUST_A', '01/01/2011',10000, 1)
INSERT INTO TestCase VALUES('Ord5', 'CUST_B','01/03/2011',20000, 12)
INSERT INTO TestCase VALUES('Ord5', 'CUST_C','01/03/2011',30000, 12)

INSERT INTO TestCase VALUES('Ord6','CUST_A', '10/01/2011',50000, 2)
INSERT INTO TestCase VALUES('Ord6','CUST_B','10/01/2011',50000, 2)

INSERT INTO TestCase VALUES('Ord7', 'CUST_A','01/03/2011',20000, 23)
INSERT INTO TestCase VALUES('Ord7', 'CUST_A','01/06/2011',20000, 42)
INSERT INTO TestCase VALUES('Ord7', 'CUST_C','01/09/2011',20000, 32)
INSERT INTO TestCase VALUES('Ord7', 'CUST_C','01/12/2011',20000, 52)

SELECT * FROM TestCase where OrdNo = 'Ord5'
ORDNO | BU    | ETA       | ORDQTY | ORDWK |
____________________________________________ 
Ord5  |CUST_A |01/01/2011 | 10000  |  1
Ord5  |CUST_B |01/03/2011 | 20000  |  12
Ord5  |CUST_C |01/03/2011 | 30000  |  12

Required Data

BU     | ETA - 01/01/2011      | ETA - 01/03/2011
_____________________________________________________
CUST_A | Order Qty  - 10000    |    ----
CUST_A | Order Week - 1-2011   |    ----
CUST_B | ----                  | Order Qty  - 20000
CUST_B | ----                  | Order Week - 12-2011
CUST_C | ----                  | Order Qty  - 30000
CUST_C | ----                  | Order Week - 12-2011

As you can see in the example above in the insert statements the combinations could be
2 BU's and 3 ETA's / sometimes 3 BU's and 1 ETA / sometimes 1 BU and 1 ETA...
All four values are dynamic. The best part is this is required only per order.
The user inputs the order no and gets the above format.

I hope I explained it well...
Regards,
Umesh
Tom Kyte

Followup  

September 15, 2011 - 7:53 am UTC

I do not understand your columns - where does 12-2011 come from ????? How did you compute your order weeks.

also, add semicolumns and to_date calls with appropriate nls_date formats so I can run your inserts - after you explain your data.

Here with proper formatting...

September 15, 2011 - 8:16 pm UTC

Reviewer: Umesh from INDIA

CREATE TABLE TestCase 
( 
OrdNo VARCHAR2(10), 
BU Varchar2(50), 
ETA date, 
OrdQty Number, 
OrdWk  Number 
) 


INSERT INTO TestCase VALUES('Ord5', 'CUST_A', '01/01/2011',10000, 1) 
INSERT INTO TestCase VALUES('Ord5', 'CUST_B','01/03/2011',20000, 12)        
INSERT INTO TestCase VALUES('Ord5', 'CUST_C','01/03/2011',30000, 12) 

INSERT INTO TestCase VALUES('Ord6','CUST_A', '10/01/2011',50000, 2) 
INSERT INTO TestCase VALUES('Ord6','CUST_B','10/01/2011',50000, 2)                  

INSERT INTO TestCase VALUES('Ord7', 'CUST_A','01/03/2011',20000, 23) 
INSERT INTO TestCase VALUES('Ord7', 'CUST_A','01/06/2011',20000, 42)        
INSERT INTO TestCase VALUES('Ord7', 'CUST_C','01/09/2011',20000, 32) 
INSERT INTO TestCase VALUES('Ord7', 'CUST_C','01/12/2011',20000, 52) 

SELECT * FROM TestCase where OrdNo = 'Ord5' 

ORDNO | BU    | ETA       | ORDQTY | ORDWK |
____________________________________________ 
Ord5  |CUST_A |01/01/2011 | 10000  |  1
Ord5  |CUST_B |01/03/2011 | 20000  |  12
Ord5  |CUST_C |01/03/2011 | 30000  |  12

Required Data

BU     | ETA - 01/01/2011      | ETA - 01/03/2011
_____________________________________________________
CUST_A | Order Qty  - 10000    |    ----
CUST_A | Order Week - 1-2011   |    ----
CUST_B | ----                  | Order Qty  - 20000
CUST_B | ----                  | Order Week - 12-2011
CUST_C | ----                  | Order Qty  - 30000
CUST_C | ----                  | Order Week - 12-2011


As you can see in the example above in the insert statements the combinations could be 
2 BU's and 3 ETA's / sometimes 3 BU's and 1 ETA / sometimes 1 BU and 1 ETA... 
All four values are dynamic. The best part is this is required only per order. 
The user inputs the order no and gets the above format.  

I hope I explained it well... 
Regards, 
Umesh 


Followup   September 15, 2011 - 7am Central time zone:

I do not understand your columns - where does 12-2011 come from ????? How did you compute your order weeks. 

also, add semicolumns and to_date calls with appropriate nls_date formats so I can run your inserts - after you explain your data. 

Hi,
Sorry about that Tom...
The Order week is just input manually (just any number) and the year is concatenated to it ...no calculation there...
Oh and the date format is dd/mm/yyyy....it ran successfully ..

The BU stands for Business Units..
and one order can be made for 1 or more BU's with different shipment dates (ETA)..
The report wanted is for 1 order at any given point of time.
And it can contain 1 or more BU's with same or different ETA's..
I hope I am clear here...


<code>
CREATE TABLE TestCase 
( 
OrdNo VARCHAR2(10), 
BU Varchar2(50), 
ETA date, 
OrdQty Number, 
OrdWk  Number 
); 


INSERT INTO TestCase VALUES('Ord5', 'CUST_A', '01/01/2011',10000, 1); 
INSERT INTO TestCase VALUES('Ord5', 'CUST_B','01/03/2011',20000, 12);        
INSERT INTO TestCase VALUES('Ord5', 'CUST_C','01/03/2011',30000, 12); 

INSERT INTO TestCase VALUES('Ord6','CUST_A', '10/01/2011',50000, 2); 
INSERT INTO TestCase VALUES('Ord6','CUST_B','10/01/2011',50000, 2);                  

INSERT INTO TestCase VALUES('Ord7', 'CUST_A','01/03/2011',20000, 23); 
INSERT INTO TestCase VALUES('Ord7', 'CUST_A','01/06/2011',20000, 42);        
INSERT INTO TestCase VALUES('Ord7', 'CUST_C','01/09/2011',20000, 32); 
INSERT INTO TestCase VALUES('Ord7', 'CUST_C','01/12/2011',20000, 52); 

SELECT * FROM TestCase where OrdNo = 'Ord5'; 

ORDNO | BU    | ETA       | ORDQTY | ORDWK |
____________________________________________ 
Ord5  |CUST_A |01/01/2011 | 10000  |  1
Ord5  |CUST_B |01/03/2011 | 20000  |  12
Ord5  |CUST_C |01/03/2011 | 30000  |  12

Required Data

BU     | ETA - 01/01/2011      | ETA - 01/03/2011
_____________________________________________________
CUST_A | Order Qty  - 10000    |    ----
CUST_A | Order Week - 1-2011   |    ----
CUST_B | ----                  | Order Qty  - 20000
CUST_B | ----                  | Order Week - 12-2011
CUST_C | ----                  | Order Qty  - 30000
CUST_C | ----                  | Order Week - 12-2011



Regards,
Umesh</code>
Tom Kyte

Followup  

September 16, 2011 - 2:01 pm UTC

Ok, if 12 is input manually, where did 1-2011 come from????? I don't understand your output.

explain in great detail what Order Week - 1-2011 and Order Week - 12-2011 mean/come from/are derived. Doesn't make sense.

pivot

September 15, 2011 - 8:39 pm UTC

Reviewer: A reader


pivot

September 15, 2011 - 8:39 pm UTC

Reviewer: A reader


pivot

September 15, 2011 - 8:39 pm UTC

Reviewer: A reader


pivot

September 15, 2011 - 8:39 pm UTC

Reviewer: A reader


Explaination of columns

September 18, 2011 - 9:05 pm UTC

Reviewer: Umesh from INDIA

Hi,

The order week is input manually but the report wanted is in the format that you pick the order week number that is input manually by the user (see the fifth column thats the Order Week after the Order Quantity) and you concatenate the current year to it. Nothing else....its the report thats wanted in the format. No calculation what so ever...

Regards,
Umesh

Tom Kyte

Followup  

September 19, 2011 - 5:46 pm UTC

nope, that does not explain the output.


I said to explain in great detail - do that and I'll respond. If you just say "it is self evident", I will not.

I see *TWO ORDER WEEKS* - ok.

do you see them?

I do

one of them is 1-2011
another is 12-2011

How did you know to do that?? In PAINSTAKING DETAIL - like you were explaining this to your mom. That level. Like a specification.


if you cannot put it in writing, you cannot possibly write the code - ever. thing about that. be precise, be specific.

Detail Explaination !!!!

September 20, 2011 - 3:22 am UTC

Reviewer: Umesh from INDIA

Hi,

OMG !!!!

Here we go once again
The data in question is the below one
INSERT INTO TestCase VALUES('Ord5', 'CUST_A', '01/01/2011',10000, 1);
INSERT INTO TestCase VALUES('Ord5', 'CUST_B','01/03/2011',20000, 12);
INSERT INTO TestCase VALUES('Ord5', 'CUST_C','01/03/2011',30000, 12);

Remember I told you that the best part is I need this report to be one order at a time.
Here I demonsrated the Ord5 which is the Order No. And of course if the Business Unit in this case
CUST_B & CUST_C are having the same ETA then obviously yoou will see 1 Order weeks (12th Week) for them.
As for CUST_A is having a different ETA hence the Order Week changes in this case 1st Week.
therefore we have 2 Order Weeks here. So when you run a select against this Order i.e., Ord5
you get the below result.

SELECT * FROM TestCase where OrdNo = 'Ord5'; 

ORDNO | BU    | ETA       | ORDQTY | ORDWK |
____________________________________________ 
Ord5  |CUST_A |01/01/2011 | 10000  |  1
Ord5  |CUST_B |01/03/2011 | 20000  |  12
Ord5  |CUST_C |01/03/2011 | 30000  |  12

For simplicity sake let's change the scenario

INSERT INTO TestCase VALUES('Ord1', 'CUST_A', '01/01/2011',10000, 1); 
INSERT INTO TestCase VALUES('Ord1', 'CUST_B','01/02/2011',20000, 5);        
INSERT INTO TestCase VALUES('Ord1', 'CUST_C','01/03/2011',30000, 12); 

SELECT * FROM TestCase where OrdNo = 'Ord1'; 

ORDNO | BU    | ETA       | ORDQTY | ORDWK |
____________________________________________ 
Ord1  |CUST_A |01/01/2011 | 10000  |  1
Ord1  |CUST_B |01/02/2011 | 20000  |  5
Ord1  |CUST_C |01/03/2011 | 30000  |  12

Required Output 

BU     | ETA - 01/01/2011      | ETA - 01/02/2011      | ETA - 01/03/2011
__________________________________________________________________________
CUST_A | Order Qty  - 10000    |    ----               |   ----
CUST_A | Order Week - 1-2011   |    ----               |   ----
CUST_B | ----                  | Order Qty  - 20000    |   ----
CUST_B | ----                  | Order Week - 5-2011   |   -----
CUST_C | ----                  |    ----               | Order Qty - 30000 
CUST_C | ----                  |    ----               | Order Week - 12-2011


I wouldnt have explained this much better I guess

Regards,
Umesh
Tom Kyte

Followup  

September 20, 2011 - 10:51 am UTC

this is your "template" query

ops$tkyte%ORA11GR2> with data as (select rownum r from dual connect by level <= 2)
  2  select bu,
  3         decode( eta, to_date( '01/01/2011', 'mm/dd/yyyy'),
  4                  decode( r, 1, 'Order Qty - ' || ordqty, 'Order Week - ' || ordwk ||'-'|| to_char(eta,'yyyy') )
  5               ) "ETA - 01/01/2011",
  6         decode( eta, to_date( '01/03/2011', 'mm/dd/yyyy'),
  7                  decode( r, 1, 'Order Qty - ' || ordqty, 'Order Week - ' || ordwk ||'-'|| to_char(eta,'yyyy') )
  8               ) "ETA - 01/03/2011"
  9   from (select * from testcase where OrdNo = 'Ord5'), data
 10   order by bu, r
 11  /

BU         ETA - 01/01/2011          ETA - 01/03/2011
---------- ------------------------- -------------------------
CUST_A     Order Qty - 10000
CUST_A     Order Week - 1-2011
CUST_B                               Order Qty - 20000
CUST_B                               Order Week - 12-2011
CUST_C                               Order Qty - 30000
CUST_C                               Order Week - 12-2011

6 rows selected.



you need to run a query to select out the distinct ETA's for your where clause - so you can then build a query that looks like this and then dynamically execute it.

see:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3868465700346538981

for the finishing touch on this.

Great Stuff Tom

September 20, 2011 - 8:03 pm UTC

Reviewer: Umesh from INDIA

Thanks a ton for this.... will work on it

Regards,
Umesh

Great Stuff Tom

September 20, 2011 - 8:03 pm UTC

Reviewer: Umesh from INDIA

Thanks a ton for this.... will work on it

Regards,
Umesh

Transposing the rows to column values

September 25, 2012 - 1:56 pm UTC

Reviewer: Raji from CA USA

Thank you very much. Your inputs were very useful in resolving an issue using max(decode()).


Display results month wise

May 28, 2013 - 4:08 am UTC

Reviewer: Boda from singapore

Hi Tom,

Pls, help to frame a query to show the results as below below:

1. Create Script for table:
CREATE TABLE "ROMP_PAWN_RET_SIP"
(
"PAWN_RET_ID" NUMBER(18,0) NOT NULL ENABLE,
"RET_MONTH" VARCHAR2(20),
"RET_YEAR" VARCHAR2(4),
"NME_OF_STAFF" VARCHAR2(100),
"PAWNSHOP_NME" VARCHAR2(100),
"UNFORMAT_ADR" VARCHAR2(180),
"PHONE_NUM" VARCHAR2(15),
"EMAIL" VARCHAR2(100),
"FAX_NUM" VARCHAR2(15),
"TOT_PLEDGES_RCD_NUM" NUMBER(13,2),
"TOT_LOAN_GRANT_AMT" NUMBER(13,2),
"TOT_PLEDGES_RED_NUM" NUMBER(13,2),
"TOT_PLEDGES_RED_AMT" NUMBER(13,2),
"INT_RCD" NUMBER(13,2),
"TOT_RCD_AMT" NUMBER(13,2),
"TOT_PLEDGES_UNRED_NUM" NUMBER(13,2),
"TOT_PLEDGES_UNRED_AMT" NUMBER(13,2),
"TOT_REALISED_AMT" NUMBER(13,2),
"TOT_PLEDGES_UNRED_FFD_NUM" NUMBER(13,2),
"TOT_FFD_AMT" NUMBER(13,2),
"TOT_REALISED_FFD_AMT" NUMBER(13,2),
"TOT_PLEDGES_RET_AMT" NUMBER(13,2),
"TOT_PLEDGES_TAK_AMT" NUMBER(13,2),
"STOCK_PLEDGES" NUMBER(13,2),
"CREATED_BY" VARCHAR2(18) NOT NULL ENABLE,
"CREATED_DTE" DATE NOT NULL ENABLE,
"LAST_UPDATED_BY" VARCHAR2(18) NOT NULL ENABLE,
"LAST_UPDATED_DTE" DATE NOT NULL ENABLE,
"RET_ID" NUMBER(18,0),
CONSTRAINT "ROMP_PAWN_RET_SIP_PK" PRIMARY KEY ("PAWN_RET_ID") ENABLE
)

2. script to insert data into above table as follows:

Insert into ROMP_PAWN_RET_SIP (PAWN_RET_ID,RET_MONTH,RET_YEAR,NME_OF_STAFF,PAWNSHOP_NME,UNFORMAT_ADR,PHONE_NUM,EMAIL,FAX_NUM,TOT_PLEDGES_RCD_NUM,TOT_LOAN_GRANT_AMT,TOT_PLEDGES_RED_NUM,TOT_PLEDGES_RED_AMT,INT_RCD,TOT_RCD_AMT,TOT_PLEDGES_UNRED_NUM,TOT_PLEDGES_UNRED_AMT,TOT_REALISED_AMT,TOT_PLEDGES_UNRED_FFD_NUM,TOT_FFD_AMT,TOT_REALISED_FFD_AMT,TOT_PLEDGES_RET_AMT,TOT_PLEDGES_TAK_AMT,STOCK_PLEDGES,CREATED_BY,CREATED_DTE,LAST_UPDATED_BY,LAST_UPDATED_DTE,RET_ID) values (85,'3','2012','Tan Hong Yee','BAN LIAN PAWNSHOP PTE LTD','1 Rochor Road #01-514 Rochor Centre Singapore 180001','62994176','admin@valuemax.com.sg','62993115',1920,3665350,1858,3179845,183246.79,3363091.79,69,74615,93794.35,3,55,62.43,0,0,11658250,'ESERVICE',to_date('11/04/12','DD/MM/RR'),'ESERVICE',to_date('11/04/12','DD/MM/RR'),217);
Insert into ROMP_PAWN_RET_SIP (PAWN_RET_ID,RET_MONTH,RET_YEAR,NME_OF_STAFF,PAWNSHOP_NME,UNFORMAT_ADR,PHONE_NUM,EMAIL,FAX_NUM,TOT_PLEDGES_RCD_NUM,TOT_LOAN_GRANT_AMT,TOT_PLEDGES_RED_NUM,TOT_PLEDGES_RED_AMT,INT_RCD,TOT_RCD_AMT,TOT_PLEDGES_UNRED_NUM,TOT_PLEDGES_UNRED_AMT,TOT_REALISED_AMT,TOT_PLEDGES_UNRED_FFD_NUM,TOT_FFD_AMT,TOT_REALISED_FFD_AMT,TOT_PLEDGES_RET_AMT,TOT_PLEDGES_TAK_AMT,STOCK_PLEDGES,CREATED_BY,CREATED_DTE,LAST_UPDATED_BY,LAST_UPDATED_DTE,RET_ID) values (315,'4','2012','Tan Hong Yee','BAN LIAN PAWNSHOP PTE LTD','1 Rochor Road #01-514 Rochor Centre Singapore 180001','62994176','admin@valuemax.com.sg','62993115',1804,3093780,1732,3140800,137484.14,3278284.14,92,77950,96858.68,8,295,334.83,0,0,11532985,'ESERVICE',to_date('16/05/12','DD/MM/RR'),'ESERVICE',to_date('16/05/12','DD/MM/RR'),545);
Insert into ROMP_PAWN_RET_SIP (PAWN_RET_ID,RET_MONTH,RET_YEAR,NME_OF_STAFF,PAWNSHOP_NME,UNFORMAT_ADR,PHONE_NUM,EMAIL,FAX_NUM,TOT_PLEDGES_RCD_NUM,TOT_LOAN_GRANT_AMT,TOT_PLEDGES_RED_NUM,TOT_PLEDGES_RED_AMT,INT_RCD,TOT_RCD_AMT,TOT_PLEDGES_UNRED_NUM,TOT_PLEDGES_UNRED_AMT,TOT_REALISED_AMT,TOT_PLEDGES_UNRED_FFD_NUM,TOT_FFD_AMT,TOT_REALISED_FFD_AMT,TOT_PLEDGES_RET_AMT,TOT_PLEDGES_TAK_AMT,STOCK_PLEDGES,CREATED_BY,CREATED_DTE,LAST_UPDATED_BY,LAST_UPDATED_DTE,RET_ID) values (619,'6','2012','Tan Hong Yee','BAN LIAN PAWNSHOP PTE LTD','1 Rochor Road #01-514 Rochor Centre Singapore 180001','62994176','admin@valuemax.com.sg','62993115',1929,3967840,1803,3741475,186628.14,3928103.14,154,152555,177372.84,6,240,272.4,0,0,11743920,'ESERVICE',to_date('05/07/12','DD/MM/RR'),'ESERVICE',to_date('05/07/12','DD/MM/RR'),1129);
Insert into ROMP_PAWN_RET_SIP (PAWN_RET_ID,RET_MONTH,RET_YEAR,NME_OF_STAFF,PAWNSHOP_NME,UNFORMAT_ADR,PHONE_NUM,EMAIL,FAX_NUM,TOT_PLEDGES_RCD_NUM,TOT_LOAN_GRANT_AMT,TOT_PLEDGES_RED_NUM,TOT_PLEDGES_RED_AMT,INT_RCD,TOT_RCD_AMT,TOT_PLEDGES_UNRED_NUM,TOT_PLEDGES_UNRED_AMT,TOT_REALISED_AMT,TOT_PLEDGES_UNRED_FFD_NUM,TOT_FFD_AMT,TOT_REALISED_FFD_AMT,TOT_PLEDGES_RET_AMT,TOT_PLEDGES_TAK_AMT,STOCK_PLEDGES,CREATED_BY,CREATED_DTE,LAST_UPDATED_BY,LAST_UPDATED_DTE,RET_ID) values (417,'5','2012','Tan Hong Yee','BAN LIAN PAWNSHOP PTE LTD','1 Rochor Road #01-514 Rochor Centre Singapore 180001','62994176','admin@valuemax.com.sg','62993115',1821,2884615,1715,2674720,125372.16,2800092.16,107,72300,90184.6,5,230,261.05,0,0,11670350,'ESERVICE',to_date('06/06/12','DD/MM/RR'),'ESERVICE',to_date('06/06/12','DD/MM/RR'),763);
Insert into ROMP_PAWN_RET_SIP (PAWN_RET_ID,RET_MONTH,RET_YEAR,NME_OF_STAFF,PAWNSHOP_NME,UNFORMAT_ADR,PHONE_NUM,EMAIL,FAX_NUM,TOT_PLEDGES_RCD_NUM,TOT_LOAN_GRANT_AMT,TOT_PLEDGES_RED_NUM,TOT_PLEDGES_RED_AMT,INT_RCD,TOT_RCD_AMT,TOT_PLEDGES_UNRED_NUM,TOT_PLEDGES_UNRED_AMT,TOT_REALISED_AMT,TOT_PLEDGES_UNRED_FFD_NUM,TOT_FFD_AMT,TOT_REALISED_FFD_AMT,TOT_PLEDGES_RET_AMT,TOT_PLEDGES_TAK_AMT,STOCK_PLEDGES,CREATED_BY,CREATED_DTE,LAST_UPDATED_BY,LAST_UPDATED_DTE,RET_ID) values (1057,'7','2012','Tan Hong Yee','BAN LIAN PAWNSHOP PTE LTD','1 Rochor Road #01-514 Rochor Centre Singapore 180001','62994176','admin@valuemax.com.sg','62993115',1725,2748525,1672,2855530,119324.38,2974854.38,88,98280,123651.12,3,90,102.15,0,1800,11536745,'ESERVICE',to_date('06/08/12','DD/MM/RR'),'ESERVICE',to_date('06/08/12','DD/MM/RR'),1861);
Insert into ROMP_PAWN_RET_SIP (PAWN_RET_ID,RET_MONTH,RET_YEAR,NME_OF_STAFF,PAWNSHOP_NME,UNFORMAT_ADR,PHONE_NUM,EMAIL,FAX_NUM,TOT_PLEDGES_RCD_NUM,TOT_LOAN_GRANT_AMT,TOT_PLEDGES_RED_NUM,TOT_PLEDGES_RED_AMT,INT_RCD,TOT_RCD_AMT,TOT_PLEDGES_UNRED_NUM,TOT_PLEDGES_UNRED_AMT,TOT_REALISED_AMT,TOT_PLEDGES_UNRED_FFD_NUM,TOT_FFD_AMT,TOT_REALISED_FFD_AMT,TOT_PLEDGES_RET_AMT,TOT_PLEDGES_TAK_AMT,STOCK_PLEDGES,CREATED_BY,CREATED_DTE,LAST_UPDATED_BY,LAST_UPDATED_DTE,RET_ID) values (1461,'8','2012','Tan Hong Yee','BAN LIAN PAWNSHOP PTE LTD','1 Rochor Road #01-514 Rochor Centre Singapore 180001','62994176','admin@valuemax.com.sg','62993115',1640,3474115,1637,3489950,185932.29,3675882.29,77,85710,102086.85,2,75,85.12,0,0,11435125,'ESERVICE',to_date('12/09/12','DD/MM/RR'),'ESERVICE',to_date('12/09/12','DD/MM/RR'),2625);
Insert into ROMP_PAWN_RET_SIP (PAWN_RET_ID,RET_MONTH,RET_YEAR,NME_OF_STAFF,PAWNSHOP_NME,UNFORMAT_ADR,PHONE_NUM,EMAIL,FAX_NUM,TOT_PLEDGES_RCD_NUM,TOT_LOAN_GRANT_AMT,TOT_PLEDGES_RED_NUM,TOT_PLEDGES_RED_AMT,INT_RCD,TOT_RCD_AMT,TOT_PLEDGES_UNRED_NUM,TOT_PLEDGES_UNRED_AMT,TOT_REALISED_AMT,TOT_PLEDGES_UNRED_FFD_NUM,TOT_FFD_AMT,TOT_REALISED_FFD_AMT,TOT_PLEDGES_RET_AMT,TOT_PLEDGES_TAK_AMT,STOCK_PLEDGES,CREATED_BY,CREATED_DTE,LAST_UPDATED_BY,LAST_UPDATED_DTE,RET_ID) values (1751,'9','2012','Tan Hong Yee','BAN LIAN PAWNSHOP PTE LTD','1 Rochor Road #01-514 Rochor Centre Singapore 180001','62994176','admin@valuemax.com.sg','62993115',1800,3037349,1779,3130339,146451.95,3276790.95,68,52340,62794.59,8,280,317.8,0,0,11289515,'ESERVICE',to_date('04/10/12','DD/MM/RR'),'ESERVICE',to_date('04/10/12','DD/MM/RR'),3222);
Insert into ROMP_PAWN_RET_SIP (PAWN_RET_ID,RET_MONTH,RET_YEAR,NME_OF_STAFF,PAWNSHOP_NME,UNFORMAT_ADR,PHONE_NUM,EMAIL,FAX_NUM,TOT_PLEDGES_RCD_NUM,TOT_LOAN_GRANT_AMT,TOT_PLEDGES_RED_NUM,TOT_PLEDGES_RED_AMT,INT_RCD,TOT_RCD_AMT,TOT_PLEDGES_UNRED_NUM,TOT_PLEDGES_UNRED_AMT,TOT_REALISED_AMT,TOT_PLEDGES_UNRED_FFD_NUM,TOT_FFD_AMT,TOT_REALISED_FFD_AMT,TOT_PLEDGES_RET_AMT,TOT_PLEDGES_TAK_AMT,STOCK_PLEDGES,CREATED_BY,CREATED_DTE,LAST_UPDATED_BY,LAST_UPDATED_DTE,RET_ID) values (2273,'10','2012','Tan Hong Yee','BAN LIAN PAWNSHOP PTE LTD','1 Rochor Road #01-514 Rochor Centre Singapore 180001','62994176','admin@valuemax.com.sg','62993115',1770,3322906,1724,3192090,165662.05,3357752.05,128,76990,91549.63,6,240,272.4,0,0,11343101,'ESERVICE',to_date('15/11/12','DD/MM/RR'),'ESERVICE',to_date('15/11/12','DD/MM/RR'),4229);
Insert into ROMP_PAWN_RET_SIP (PAWN_RET_ID,RET_MONTH,RET_YEAR,NME_OF_STAFF,PAWNSHOP_NME,UNFORMAT_ADR,PHONE_NUM,EMAIL,FAX_NUM,TOT_PLEDGES_RCD_NUM,TOT_LOAN_GRANT_AMT,TOT_PLEDGES_RED_NUM,TOT_PLEDGES_RED_AMT,INT_RCD,TOT_RCD_AMT,TOT_PLEDGES_UNRED_NUM,TOT_PLEDGES_UNRED_AMT,TOT_REALISED_AMT,TOT_PLEDGES_UNRED_FFD_NUM,TOT_FFD_AMT,TOT_REALISED_FFD_AMT,TOT_PLEDGES_RET_AMT,TOT_PLEDGES_TAK_AMT,STOCK_PLEDGES,CREATED_BY,CREATED_DTE,LAST_UPDATED_BY,LAST_UPDATED_DTE,RET_ID) values (2596,'11','2012','Tan Hong Yee','BAN LIAN PAWNSHOP PTE LTD','1 Rochor Road #01-514 Rochor Centre Singapore 180001','62994176','admin@valuemax.com.sg','62993115',1743,2888800,1685,2829795,127820.31,2957615.31,113,129325,159461.37,3,120,136.2,0,0,11272661,'ESERVICE',to_date('17/12/12','DD/MM/RR'),'ESERVICE',to_date('17/12/12','DD/MM/RR'),4962);

Pls, help to have a query which will display the results monthly (jan-dec) for each pawnshop_nme having total(jan-dec) as one of the col and Avg (total/12) and last col as 1.5times of avg as follows:

PAWNSHOP_NME JAN($) FEB MAR APR MAY JUN JULY AUG SEP OCT NOV DEC TOTAL($) AVG 1.5 TIMES OF AVG
BAN LIAN PAWNSHOP PTE LTD - - 3665350 3093780 2884615 3967840 2748525 3474115 3037349 3322906 2888800 - 29083280 2423607 3635410.50


NOTE: The amount shown for each of the month for particular year for a particular pawnshop_nme is from 'TOT_LOAN_GRANT_AMT' field



Thanks in Avdavance
Boda



Tom Kyte

Followup  

May 29, 2013 - 6:57 pm UTC

given the above original answer... you cannot do this yourself? honestly?


you've got a ton of columns in your table, but yet you don't say which columns are actually useful in the output :(

and you store numbers in strings :(((((

and you store dates hidden in numbers hidden in strings :(((((((((((((((((

I'm not sure if this could be more wrong...


get rid of the columns that are not relevant.
tell me what to add up to get to the columns you actually need (explain your output)

and first - try it yourself, pivoting is pretty easy - the method is shown above, if you can understand how it works, you'll be able to use it all over the place!

Pivot Query

May 30, 2013 - 12:43 am UTC

Reviewer: Boda from Singapore

Hi Tom,

Sorry for unclear posting. In want following columns in my output:

1. PAWNSHOP_NME
2. RET_MONTH (01 as Jan, 02 as Feb, 03 as Mar and so on) and value to be dispalyed for this col(RET_MONTH) is 'TOT_LOAN_GRANT_AMT' field
3. TOTAL($) (This is the total of all months 01-12 for each PAWNSHOP_NME for particular RET_YEAR)
4. AVG($) (This is TOTAL/12)
5. 1.5AVG($) (This is AVG*1.5)

I want output as follows:

PAWNSHOP_NME JAN FEB MAR APR MAY JUN JULY AUG SEP OCT NOV DEC TOTAL($) AVG 1.5TIMESOFAVG
------------ --- --- --- --- --- ---- --- --- ---- --- --- ---- ------- ----- ----------
TEST 100 50 20 89 70 800 800 - - 800 99 90 2198 243.16 364.71


Thanks in Advance
Boda

Tom Kyte

Followup  

May 30, 2013 - 2:36 pm UTC

and you seriously cannot figure out how to do this on your own given the examples on this page? really?

please do what I said - cut down on the number of columns in the example (make it easy for me)

make sure your code actually runs (you didn't leave any white space in there, your column names are all broken) - again, make it easy for us


tell how how to compute this magical total. So, this is computed by PAWNSHOP_NME and RET_MONTH and RET_YEAR. Great, perfect. However WHAT IS IT COMPUTED FROM, total = ?????? there is no column named total, so therefore total is an expression. What comprises this magic total?????????????????????????????


what happens if there is more than one year in there? do we double up the data in JAN ? or should the report actually include RET_YEAR going down the page too.



and seriously, if you have a result set:

name   month   total
-----  -----   ------
x      1       1000
x      2       2000
...
y      1       5321
y      2       56432
....
z      12      14321




which you must be able to produce (and right now -- only you can since only you know what total is...)

you cannot yourself figure out:

select name, 
       max( decode( month, 1, total ) ) "JAN",
       max( decode( month, 2, total ) ) "FEB",
       .....
       max( decode( month, 12, total ) ) "DEC",
       sum( total )  "TOTAL_TOTAL",
       avg( total ) "AVG_TOTAL",
       1.5*avg( total ) "1.5TIMESOFAVG"
  from (your_query_from_above)
 group by name


based on the above technique????? Please - give it a try on your own - it is important.



If I show you exactly how to do on query - you'll be able to do that one query (by copying it)


If I show you HOW IN GENERAL to apply a technique, you'll be able to apply that technique to millions of queries.


Let's go for that last point please.

May 30, 2013 - 3:05 pm UTC

Reviewer: A reader

Just like fishing, Tom wont give you the fish

Pivot

June 02, 2013 - 9:28 am UTC

Reviewer: Boda from Singapore

Thanks Tom, this is want I want and Thanks for your sincere advice and I will keep in mind.
Tom Kyte

Followup  

June 03, 2013 - 2:33 pm UTC

it is funny, it is the same exact query I have in the original answer........

you had the answer all along, before you even asked!!! it was there already!


Lovely

February 23, 2015 - 10:54 am UTC

Reviewer: A reader from London, UK

Lovely thanks this really helped me!!

GREAT SOLUTION!

September 02, 2015 - 6:25 pm UTC

Reviewer: Rod from NB, CANADA