Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Mike.

Asked: May 26, 2002 - 1:58 pm UTC

Last updated: October 28, 2012 - 11:17 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

To be brief I frequently am asked to produce un-pivots of data *and* calculate
values. These calculations may also vary by department. Hard coding these
views for a rather fluid calculation system and then to keep versions per
department wouldn't be feasible as our department structures and calculation models change monthly; hard-coding would be maintenance prohibative since I am the only developer for the company.

I have read your examples in your excellent book on Analytic Functions (way
cool) but they deal with pivoting a table (rows to columns) not columns to rows.
Because of this I have resorted to writting my own domain lookup function that
uses bind variables and a lookup table that allows my calculation model to vary
by department.

Rather than hard-coding a view I wrote a small
domain lookup function that allows me flexibility (at the expense of cpu
time). I simply wanted to share the function and see if you
had any comments and to see if you might show me how to get rid of the dlookup
function by hardcoding the below example into a simple view that can un-pivot
the data *and* calculate values per row per deptment *and* stay flexible. At minimum I thought my
small function might be useful for other readers. See below for an example of
un-pivoting a table that seems more maintainable to me than using static views
if your view needs to change frequently.


Pretend that I have given two departments id's 1 and 2 and that the values of a
and b are important to a calculation they need. Please note that at any time a
department may appear or disappear and that each department calculates their
values differently.

temp table
dept a b
=======================================
1 1 2
2 4 5


Per department, per column this is the calculation that each one wants.

temp_lkup table
dept col calc
=======================================
1 a a
1 b b*2
2 a a+6
2 b b*3

desired output
dept col orig calc val
=======================================
1 a 1 a 1
1 b 2 b*2 4
2 a 4 a+6 10
2 b 5 b*3 15


<script>
create table temp (dept integer, a integer, b integer)
/
insert into temp select 1,1,2 from dual
/
insert into temp select 2,4,5 from dual
/
commit
/

select * from temp
/

create table temp_lkup (dept integer, col varchar2(16), calc varchar2(16))
/
insert into temp_lkup select 1, 'a', 'a' from dual
/
insert into temp_lkup select 1, 'b', 'b*2' from dual
/
insert into temp_lkup select 2, 'a', 'a+6' from dual
/
insert into temp_lkup select 2, 'b', 'b*3' from dual
/
commit
/
select * from temp_lkup
/

CREATE OR REPLACE FUNCTION DLOOKUP_BIND_ONE (vField in
varchar2, vTable in varchar2, vWhere in varchar2 default NULL,
vBind in varchar2) return varchar2
AUTHID CURRENT_USER
as
vVar varchar2(4000);
vQuery varchar2(4000);
begin

-- exit if field is null
if vfield is null then
return null;
end if;

-- exit if table is null
if vtable is null then
return null;
end if;

vQuery := 'select '||vField||' from '||vTable;
if (vWhere is not NULL) then
vQuery := vQuery ||' where '||vWhere;
end if;
execute immediate vQuery into vVar using vBind;
return vVar;
exception
when NO_DATA_FOUND then
return null;
when others then
raise;
end;
/

-- now to get the result set
select temp.dept, temp_lkup.col, temp_lkup.calc,
to_number(nvl(dlookup_bind_one(col, 'temp', 'dept=:a', temp.id),0)) orig,
to_number(nvl(dlookup_bind_one(calc, 'temp', 'dept=:a', temp.id),0)) val
from temp, temp_lkup
where temp.dept = temp_lkup.dept
/
</snip>
DEPT COL CALC ORIG VAL
---------- ---------------- ---------------- ---------- ----------
1 a a 1 1
1 b b*2 2 4
2 a a+6 4 10
2 b b*3 5 15


Any ideas as to how I could improve the efficiency of this code but still keep the lookup of the calculations per deptartment in a table? Thanks.


and Tom said...

I might do it with ROWID:


CREATE OR REPLACE
FUNCTION DLOOKUP_BIND_ONE(vField in varchar2,
vTable in varchar2,
vRowid in rowid ) return varchar2
AUTHID CURRENT_USER
as
vVar varchar2(4000);
begin
if ( vField is null )
then
return null;
end if;
execute immediate
'select ' || vField ||
' from ' || vTable ||
' where rowid = :rowid'
into vVar using vRowid;

return vVar;
end;
/

-- now to get the result set
select temp.dept, temp_lkup.col, temp_lkup.calc,
to_number(nvl(dlookup_bind_one(col, 'temp', temp.rowid ),0)) orig,
to_number(nvl(dlookup_bind_one(calc, 'temp', temp.rowid ),0)) val
from temp, temp_lkup
where temp.dept = temp_lkup.dept
/

rowid being faster than keyed reads (and obviates the need for a where clause to be passed as well).

I removed the no_data_found cause it seems to me that that would be a big old bug if it returned zero rows. I would hate to get a "everything is OK" result from my function in that case.

but otherwise it works -- I would suggest setting session_cached_cursors=N when using this approach since ref cursors are not cached like other plsql cursors are (and you'll be soft parsing like mad with this)

Rating

  (20 ratings)

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

Comments

Thanks!

Mike, May 28, 2002 - 2:22 pm UTC

I set cusor_sharing = force for the account that runs this and I have watched the parse stats during the run and I don't seem to be suffering the soft parse hit. Although I didn't know that about REF Cursors not being cached.

As for ROWID (duh?). Thanks, I didn't even think about that. Thanks again.


Tom Kyte
May 28, 2002 - 7:55 pm UTC

Oh you are -- every row returned is doing a soft parse! If you return 1000 records -- you'll have done 1,000 soft parses. It is there.

how to unpivot this

A reader, April 23, 2003 - 1:55 pm UTC

in some example on this site you have

pivoted a table and got this result

STATUS 0 1 2 3 4 5 6 7 8+
====================================================
FINAL ACTION 0 0 1 0 0 0 0 0 1


how can I unpivot it in a single sql stmt ?

2.) how can I get

1
13
10
any static values ( that i give)
using select stmt..

eg.
select t from dual
which should give me
1
13
10
in column not in row with out using physical table

Tom Kyte
April 23, 2003 - 7:52 pm UTC

if there 9 columns to "unpivot" then


select c1,
decode( r, 1, value1, 2, value2, 3, value3, ...., 9, value9 )
from T, /* table with 9 columns to unpivot */
(select rownum r from all_objects where rownum <= 9 )


2) eh? why 1, 13, 10.

(dual is by the way -- a physical honest to goodness table -- at least as of 2003)

but -- search this site for str2table, you'll see how


select * from TABLE( cast(str2table('1,13,10') as myTableType) );

can work for you.

can't fine it

A reader, April 24, 2003 - 12:34 pm UTC

I searched for str2table got 7 results and

all of them are saying search for str2table
i did not find the def. ?

can you give me link ?
or put it in asktom.oracle.com/~tkyte

Tom Kyte
April 24, 2003 - 12:40 pm UTC

funny, i got 6 and the first one:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:110612348061 <code>

hit it.

thanks,

A reader, April 24, 2003 - 1:14 pm UTC



That's because I searched, as you've said, for
**str2table**

and you searced on (str2tbl) may be...

Thanks, any way

Tom Kyte
April 24, 2003 - 1:36 pm UTC

nope, searched for str2table - just did it again to test.

How to do this - columns to rows

subramanian, July 22, 2003 - 8:51 pm UTC

I have a table with 5 columns.
say trans_dt, col1, col2, col3, col4.
The first column is of date type and the rest are number types.
Normal select would give the following results.

Date displayed in dd.mm.yyyy format.

Trans_dt col1 col2 col3 col4
-------- ------ ----- ------ --------
31.03.2000 100 200 300 400
31.03.2001 500 600 700 800
31.03.2002 400 300 200 900
31.03.2003 500 100 300 200

Could you give me a query to display the
results in the format given below

trans_dt trans_dt trans_dt
col1
col2
col3
col4

Tom Kyte
July 23, 2003 - 7:44 am UTC 

that is generally the domain of a reporting tool.  However, if you KNOW the range of trans_dt, you can do it. the following walks through a transpose and pivot:

ops$tkyte@ORA920LAP> select * from t;

TRANS_DT          C1         C2         C3         C4
--------- ---------- ---------- ---------- ----------
23-JUL-03        432       3823        159       7175
23-JUL-02       9356       2635       5324       6586
23-JUL-01       7073        279       3574       4816
23-JUL-00       1510       6416       5987       9003

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> select trans_dt,
  2         'c' || r c,
  3             decode( r, 1, c1, 2, c2, 3, c3, 4, c4 ) c_val
  4    from t,
  5         (select rownum r
  6                from all_objects
  7                   where rownum <= 4)
  8  /

TRANS_DT  C          C_VAL
--------- ----- ----------
23-JUL-03 c1           432
23-JUL-02 c1          9356
23-JUL-01 c1          7073
23-JUL-00 c1          1510
23-JUL-03 c2          3823
23-JUL-02 c2          2635
23-JUL-01 c2           279
23-JUL-00 c2          6416
23-JUL-03 c3           159
23-JUL-02 c3          5324
23-JUL-01 c3          3574
23-JUL-00 c3          5987
23-JUL-03 c4          7175
23-JUL-02 c4          6586
23-JUL-01 c4          4816
23-JUL-00 c4          9003

16 rows selected.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> select c,
  2         ( decode( trans_dt, to_date('23-jul-2003'), c_val ) ) c2003,
  3         ( decode( trans_dt, to_date('23-jul-2002'), c_val ) ) c2002,
  4         ( decode( trans_dt, to_date('23-jul-2001'), c_val ) ) c2001,
  5         ( decode( trans_dt, to_date('23-jul-2000'), c_val ) ) c2000
  6    from (
  7  select trans_dt,
  8         'c' || r c,
  9             decode( r, 1, c1, 2, c2, 3, c3, 4, c4 ) c_val
 10    from t,
 11         (select rownum r
 12                from all_objects
 13                   where rownum <= 4)
 14         )
 15  /

C          C2003      C2002      C2001      C2000
----- ---------- ---------- ---------- ----------
c1           432
c1                     9356
c1                                7073
c1                                           1510
c2          3823
c2                     2635
c2                                 279
c2                                           6416
c3           159
c3                     5324
c3                                3574
c3                                           5987
c4          7175
c4                     6586
c4                                4816
c4                                           9003

16 rows selected.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> select c,
  2         max( decode( trans_dt, to_date('23-jul-2003'), c_val ) ) c2003,
  3         max( decode( trans_dt, to_date('23-jul-2002'), c_val ) ) c2002,
  4         max( decode( trans_dt, to_date('23-jul-2001'), c_val ) ) c2001,
  5         max( decode( trans_dt, to_date('23-jul-2000'), c_val ) ) c2000
  6    from (
  7  select trans_dt,
  8         'c' || r c,
  9             decode( r, 1, c1, 2, c2, 3, c3, 4, c4 ) c_val
 10    from t,
 11         (select rownum r
 12                from all_objects
 13                   where rownum <= 4)
 14         )
 15   group by c
 16  /

C          C2003      C2002      C2001      C2000
----- ---------- ---------- ---------- ----------
c1           432       9356       7073       1510
c2          3823       2635        279       6416
c3           159       5324       3574       5987
c4          7175       6586       4816       9003

ops$tkyte@ORA920LAP>

Subramanian, July 23, 2003 - 8:37 pm UTC

Thank you very much.
This is what I wanted.


great answer

Alessandro, December 14, 2004 - 9:57 am UTC


pivot a result set

Gowri, May 24, 2006 - 3:59 am UTC

Its really helpful me in getting the desired result set in time.

Slightly different situation

Geetha, May 25, 2006 - 2:22 pm UTC

I have a different situation greatly appreciate your help in advance.

I have an ID

12345
67890
66666

ID has many licenses in different states

12345 WA 12222 1/1/2000
12345 WA 32222 1/1/2001
12345 AK 51111 1/1/1999
67890 WA 61111 1/1/2000
67890 ID 71111 1/1/2001
66666 WA 70000 1/1/2000
66666 WA 90000 1/1/2001
66666 AK 80000 1/1/2002

The rules I have are as follows,
I need to fetch 2 licenses only.
The precendence is all WA states comes first.
I need the latest dated license numbers.

The result needed is in the following format...

12345 WA 32222 1/1/2001 WA 12222 1/1/2000
67890 WA 61111 1/1/2000 ID 71111 1/1/2001
66666 WA 90000 1/1/2001 AK 80000 1/1/2002
-- Note even though 66666 has 2 WA records, AK should be displayed because that has the latest date.

Is this even doable in one SQL statement?

Tom Kyte
May 25, 2006 - 5:47 pm UTC

yes, this is doable in a single sql statement....


absolutely doable....

and I'd even show you how to use row_number() over (partition by id order by case when state = 'WA' then 1 else 2 end, date_col DESC ) to do it

if I only had a table and some inserts...

but - maybe that is enough hints to get you started on your own..

Wonderful...

Geetha, May 30, 2006 - 2:10 pm UTC

I have got the desired result. Let me know if I got it right or is there a better way to do it.

In realtime this would be performed for 70,000 records (IDs) am hoping it would not affect performance?

Thanks a lot for the wonderful response. After I got the "doable" from you (THE GURU himself) is when I pursued this further. :)

Here is the script...

drop table aa
/

drop table ab
/

create table aa (id number)
/

insert into aa values (12345)
/

insert into aa values (67890)
/

insert into aa values (66666)
/

create table ab (id number, state varchar2(2), license number, dt date)
/

insert into ab values (12345, 'WA', 12222, to_date('1/1/2000','mm/dd/yyyy'))
/

insert into ab values (12345, 'WA', 32222, to_date('1/1/2001','mm/dd/yyyy'))
/

insert into ab values (12345, 'AK', 51111, to_date('1/1/1999','mm/dd/yyyy'))
/

insert into ab values (67890, 'WA', 61111, to_date('1/1/2000','mm/dd/yyyy'))
/

insert into ab values (67890, 'ID', 71111, to_date('1/1/2001','mm/dd/yyyy'))
/

insert into ab values (66666, 'WA', 70000, to_date('1/1/2000','mm/dd/yyyy'))
/

insert into ab values (66666, 'WA', 90000, to_date('1/1/2001','mm/dd/yyyy'))
/

insert into ab values (66666, 'AK', 80000, to_date('1/1/2002','mm/dd/yyyy'))
/

commit
/

select aa_t.id,
max(aa_t.first_state) first_state,
max(aa_t.first_license) first_license,
max(aa_t.first_dt) first_dt,
max(aa_t.second_state) second_state,
max(aa_t.second_license) second_license,
max(aa_t.second_dt) second_dt
from
(select aa.id,
decode(ab_t.orderby, 1, ab_t.state) first_state,
decode(ab_t.orderby, 1, ab_t.license) first_license,
decode(ab_t.orderby, 1, ab_t.dt) first_dt,
decode(ab_t.orderby, 2, ab_t.state) second_state,
decode(ab_t.orderby, 2, ab_t.license) second_license,
decode(ab_t.orderby, 2, ab_t.dt) second_dt
from aa,
(select ab.id,
ab.state,
ab.license,
ab.dt,
row_number()
over (partition by
ab.id order by
dt desc) orderby
from ab
where state = 'WA') ab_t
where ab_t.orderby in (1, 2)
and aa.id = ab_t.id)aa_t
group by aa_t.id
/
ID FI FIRST_LICENSE FIRST_DT SE SECOND_LICENSE SECOND_DT
---------- -- ------------- --------- -- -------------- ---------
12345 WA 32222 01-JAN-01 WA 12222 01-JAN-00
66666 WA 90000 01-JAN-01 WA 70000 01-JAN-00
67890 WA 61111 01-JAN-00

Tom Kyte
May 30, 2006 - 6:43 pm UTC

70,000 records is pretty small - subsecond? maybe not, maybe yes, but pretty fast.

This is the right way, the way I was alluding to (you could, but don't have to, remove a layer of inline views by doing max( decode( ... )) )

row_number() applied against 'WA' only ...

Gabe, May 31, 2006 - 12:04 am UTC

What happened to ... <quote>Note even though 66666 has 2 WA records, AK should be displayed because that has the latest date</quote>?

Don't think all is done here.

What about this ?

Michel Cadot, May 31, 2006 - 2:58 am UTC 

If i clearly understand the problem, you always have the last license then all the WA ones from the last one to the first one and then the other licenses in the same order. 
Finally you display it with WA first even if it is not the last date.

This can be done with:

SQL> with 
  2    step1 as ( -- number the rows
  3      select id, state, license, dt,
  4             row_number() 
  5               over (partition by id order by dt desc) rndt,
  6             row_number() 
  7               over (partition by id 
  8                     order by decode(state,'WA',1,2), dt desc) rnst
  9      from ab
 10    ),
 11    step2 as ( -- keep only potential ones and number them
 12      select id, state, license, dt,
 13             case 
 14             when rndt = 1 and state = 'WA' then 1
 15             when rndt = 1 and state != 'WA' then 2
 16             when lag(state) over (partition by id order by rndt)
 17                    = 'WA' 
 18               then rndt
 19             else rndt-1
 20             end rn
 21      from step1
 22      where rndt = 1 or rnst <= 2
 23    )
 24  select id, 
 25         max(decode(rn,1,state)) first_state,
 26         max(decode(rn,1,license)) first_license,
 27         max(decode(rn,1,dt)) first_dt,
 28         max(decode(rn,2,state)) second_state,
 29         max(decode(rn,2,license)) second_license,
 30         max(decode(rn,2,dt)) second_dt
 31  from step2
 32  where rn <= 2  -- only the two first rows per id
 33  group by id
 34  order by id
 35  /
        ID FI FIRST_LICENSE FIRST_DT  SE SECOND_LICENSE SECOND_DT
---------- -- ------------- --------- -- -------------- ---------
     12345 WA         32222 01-JAN-01 WA          12222 01-JAN-00
     66666 WA         90000 01-JAN-01 AK          80000 01-JAN-02
     67890 WA         61111 01-JAN-00 ID          71111 01-JAN-01

Regards
Michel

Absolutely...

Geetha, May 31, 2006 - 2:03 pm UTC

Gabe, you are right. I had a different set of requirements to begin with the one exactly answered by Michael. (That was very cool Michael)
Now the requirements have changed (!@#$ happens all the time, don't they) they want only 2 latest dated WA licenses.
Thanks Tom, Gabe and Michael for the wonderful help.


comma separated columns,

A reader, June 09, 2006 - 1:19 pm UTC

I have a table (say XYZ) that stores the column names of other tables (say ABC,PQR,EFG)

select * from xyz;

TABLE_NAME COLUMN_NAMES
---------- -----------
ABC C1,C2
PQR C1,C2,C3
EFG C2

Our purpose is to create the primary key on the tables ABC,PQR,EFG for those columns listed in the COLUMN_NAMES attribute in XYZ table.

The column_names is comma separated.

Prior to creating the primary key we have to check for duplicates (if it exists delete the duplicate records), check for null values (delete the record if it has null value) and then create a primary key.

I am able to check for the duplicate records dynamically in my pl/sql program but I am stuck on how to figure out whether there is a null value in any of the column names.

Your help is highly appreciated.

thanks,



Tom Kyte
June 09, 2006 - 1:36 pm UTC

can we fix your data model and correct this before it gets too widely used????


This data model is so very flawed in so many ways.


(and you know the add primary key command can find your records in error using "exceptions into"

less code = less bugs, more time to fix your data model!
more code = more bugs, less time to do the important stuff!

and I'm seriously curious as to how you know automagically what record to delete upon finding a duplicate primary key - if it is "any of them", then why not delete everything? Your data is already rather "random")

Very useful

Vinayak Pai, July 03, 2006 - 4:21 am UTC

I wrote the code to understand your query. At first shot I did not understand your query ;)

The way you gor r is really cool :)

CREATE TABLE t
(trans_dt date,c1 number,c2 number ,c3 number ,c4 number)

INSERT INTO t(trans_dt,c1,c2,c3,c4)
values ('23-JUL-03',432,3823,159,7175);

INSERT INTO t(trans_dt,c1,c2,c3,c4)
values ('23-JUL-02',9356,2635,5324,6586);

INSERT INTO t(trans_dt,c1,c2,c3,c4)
values ('23-JUL-01',7073,279,3574,4816);

INSERT INTO t(trans_dt,c1,c2,c3,c4)
values ('23-JUL-00',1510,6416,5987,9003);


select trans_dt,
'c' || r c,
decode( r, 1, c1, 2, c2, 3, c3, 4, c4 ) c_val
from t,
(select rownum r
from all_objects
where rownum <= 4)

Reader, January 23, 2009 - 1:00 pm UTC 

create table tst
(first_nm varchar2(20)
,last_nm varchar2(20)
);

insert into tst
values
('system1','redhat');

insert into tst
values
('system2','ubuntu');

insert into tst
values
('system3','mac');

commit;

SQL> select * from tst;

FIRST_NM             LAST_NM
-------------------- --------------------
system1              redhat
system2              ubuntu
system3              mac

I want to display as follows:

values
------------------------
system1 |system2 |system3
redhat |ubuntu |mac

can you please let me know how to write query for this?

Tom Kyte
January 23, 2009 - 2:06 pm UTC 


ops$tkyte%ORA10GR2> select decode( col, 1, fname, 2, lname ) data
  2    from (
  3  select substr(sys_connect_by_path(first_nm,'|'),2) fname,
  4         substr(sys_connect_by_path(last_nm,'|'),2)  lname
  5    from (
  6  select first_nm, last_nm, row_number() over (order by first_nm) rn, count(*) over () cnt
  7    from tst
  8         )
  9   where rn = cnt
 10   start with rn = 1
 11  connect by prior rn+1 = rn
 12         ),
 13             (select 1 col from dual union all select 2 col from dual)
 14  /

DATA
------------------------------
system1|system2|system3
redhat|ubuntu|mac

Reader, January 25, 2009 - 9:11 am UTC

Tom,
In regards to the above question, can you please let me know if the query can be written without using sys_connect_by_path function?
Tom Kyte
January 25, 2009 - 11:31 am UTC

yes, it can.

I know of at least two or three more approaches. One assumes you know the number of rows in the table upfront - the other relies on your ability to install a small bit of plsql.

search site for
pivot

or search site for
stragg

Reader, April 13, 2009 - 2:47 pm UTC

create table tab_tst
(val varchar2(20)
,min_price number
,max_price number
,ts_created timestamp(6)
);

insert into tab_tst
values
('ABC',100,200,to_timestamp('20090401 9:00:00.000','yyyymmdd hh24:mi:ss.ff'));

insert into tab_tst
values
('ABC',200,300,to_timestamp('20090401 9:20:00.000','yyyymmdd hh24:mi:ss.ff'));

insert into tab_tst
values
('ABC',300,400,to_timestamp('20090401 9:30:00.000','yyyymmdd hh24:mi:ss.ff'));

insert into tab_tst
values
('ABC',300,400,to_timestamp('20090401 15:00:00.000','yyyymmdd hh24:mi:ss.ff'));

insert into tab_tst
values
('ABC',300,400,to_timestamp('20090401 15:15:00.000','yyyymmdd hh24:mi:ss.ff'))

insert into tab_tst
values
('PQR',400,500,to_timestamp('20090401 9:30:00.000','yyyymmdd hh24:mi:ss.ff'));

insert into tab_tst
values
('PQR',500,600,to_timestamp('20090401 12:00:00.000','yyyymmdd hh24:mi:ss.ff'));

insert into tab_tst
values
('PQR',600,800,to_timestamp('20090401 13:00:00.000','yyyymmdd hh24:mi:ss.ff'));

commit;

I have to get results like this:

dt VAL STR sl_no
20090401 ABC 09:00|ABC|100|200&09:20|ABC|200|300&09:300|ABC|300|400& 0
20090401 ABC 15:00|ABC|300|400&15:15|300|400& 1
20090401 PQR 09:30|PQR|400|500&12:00|500|600&13:00|PQR|600|800 0


Each record for ABC can have 3 rows combined by pipe and seperated by ampersand with sl_no starting with 0. Can you tell how to arrive at this?
Tom Kyte
April 13, 2009 - 5:43 pm UTC

you have not described this output sufficiently for anyone to make heads or tails of it.

You need many more words here.


it seems you might want to pivot on the hour? I'm guessing, totally.

or maybe, group every three records together? I don't know.

what the heck is sl_no and where did it come from. It just sort of appears in the output? Or are we supposed to assign an increasing sl_no to each output record with the same "val"?

Reader, April 13, 2009 - 5:47 pm UTC

Hello Tom,
Sorry for not sending the output correctly.

I need to sort the data by hour:minute and then group 3 records into one. I need to add an
additional column in the end once the grouping is done starting with 0 which is the sl_no.


Tom Kyte
April 14, 2009 - 9:26 am UTC

it is not that you did not send the output correctly, it is that the question - as phrased - had lots of ambiguity to it, anyone that answered would be GUESSING as to what you really want.

and it is still ambiguous. Ok - we sort by hour:minute (really or do we sort by timestamp - including the DAY???? you have a date in the output...).

Ok, we group three records into one. But, you have this DAY field in the output. Do you mean to use a key of YYYYMMDD + VAL - do we partition the data by those two elements - or is it just by VAL. If just by VAL, how do we derive the DT field in the output???????


Not sure what you want - really. Phrasing requirements, explaining precisely what you need to do, is 99% of what our jobs are. Writing code - EASY. Getting specifications you can write code from - darn near impossible sometimes.


I'll guess - you mean to break the data up by trunc(ts_created,'dd') and val.

Then, order the data within each group by ts_created from small to big.

Then taking each set of records three at a time in each group - pivot them.


If that is not what you want, you can figure out what to do from the following - since you would never use code you don't understand, you'll understand what it is doing - observe the technique and then you can apply that technique to solve your problem - if I guessed wrong.



ops$tkyte%ORA10GR2> select to_char(dt,'yyyymmdd'),
  2         val,
  3             ts0||'|'||val||'|'||min_price0||'|'||max_price0||'&' ||
  4             case when ts1 is not null then ts1||'|'||val||'|'||min_price1||'|'||max_price1||'&' end ||
  5             case when ts2 is not null then ts2||'|'||val||'|'||min_price2||'|'||max_price2||'&' end str,
  6             sl_no
  7    from (
  8  select dt,
  9         val,
 10         max(decode(rn2,0,min_price)) min_price0,
 11         max(decode(rn2,0,max_price)) max_price0,
 12         max(decode(rn2,0,ts)) ts0,
 13         max(decode(rn2,1,min_price)) min_price1,
 14         max(decode(rn2,1,max_price)) max_price1,
 15         max(decode(rn2,1,ts)) ts1,
 16         max(decode(rn2,2,min_price)) min_price2,
 17         max(decode(rn2,2,max_price)) max_price2,
 18         max(decode(rn2,2,ts)) ts2,
 19         sl_no
 20    from (
 21  select val, min_price, max_price, to_char(ts_created,'hh24:mi') ts, trunc(ts_created,'dd') dt,
 22         row_number() over (partition by val, trunc(ts_created,'dd') order by ts_created) rn,
 23         mod(row_number() over (partition by val, trunc(ts_created,'dd') order by ts_created)-1,3) rn2,
 24         trunc((row_number() over (partition by val, trunc(ts_created,'dd') order by ts_created)-0.1)/3) sl_no
 25    from tab_tst
 26         )
 27   group by dt, val, sl_no
 28         )
 29   order by dt, val, sl_no
 30  /

TO_CHAR( VAL                  STR                                                            SL_NO
-------- -------------------- --------------------------------------------------------- ----------
20090401 ABC                  09:00|ABC|100|200&09:20|ABC|200|300&09:30|ABC|300|400&             0
20090401 ABC                  15:00|ABC|300|400&15:15|ABC|300|400&                               1
20090401 PQR                  09:30|PQR|400|500&12:00|PQR|500|600&13:00|PQR|600|800&             0

For the same scenario please let me know the sql if the rows count changes dynmically

A reader, October 28, 2012 - 2:24 pm UTC 

select * from t;

TRANS_DT          C1         C2         C3         C4
--------- ---------- ---------- ---------- ----------
23-JUL-03        432       3823        159       7175
23-JUL-02       9356       2635       5324       6586
23-JUL-01       7073        279       3574       4816
23-JUL-00       1510       6416       5987       9003

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> select trans_dt,
  2         'c' || r c,
  3             decode( r, 1, c1, 2, c2, 3, c3, 4, c4 ) c_val
  4    from t,
  5         (select rownum r
  6                from all_objects
  7                   where rownum <= 4)
  8  /

TRANS_DT  C          C_VAL
--------- ----- ----------
23-JUL-03 c1           432
23-JUL-02 c1          9356
23-JUL-01 c1          7073
23-JUL-00 c1          1510
23-JUL-03 c2          3823
23-JUL-02 c2          2635
23-JUL-01 c2           279
23-JUL-00 c2          6416
23-JUL-03 c3           159
23-JUL-02 c3          5324
23-JUL-01 c3          3574
23-JUL-00 c3          5987
23-JUL-03 c4          7175
23-JUL-02 c4          6586
23-JUL-01 c4          4816
23-JUL-00 c4          9003

16 rows selected.

For the same scenario please let me know the sql if the rows count changes dynmically

Tom Kyte
October 28, 2012 - 11:17 pm UTC

I don't know what you mean, it doesn't matter how many rows you have in T?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library