Brilliant
Andrew Gilfrin, December 16, 2002 - 10:53 am UTC
Had a requirement here to produce a pivot table effect,
had everybody stumped but using this post and Toms's book managed to meet the requirement.
As usual Tom rocks big time.
A Date Column to pivit into Week1, Week2.....
Robert, December 16, 2002 - 12:58 pm UTC
I think I can definitely achieve what I need based on your
solutuin here.
But I have a date col I need to pivot into something like:
Count, Week1, Week2....
Is there a way to return 1/4/02 as 1 (week 1), 1/16/02 as 2...etc
Thanks
December 16, 2002 - 1:46 pm UTC
ops$tkyte@ORA817DEV.US.ORACLE.COM> select dt, trunc(dt,'iw')
2 from ( select to_date('01/01/2002','dd/mm/yyyy')+rownum-1 dt from all_objects where rownum < 30 )
3 /
DT TRUNC(DT,
--------- ---------
01-JAN-02 31-DEC-01
02-JAN-02 31-DEC-01
03-JAN-02 31-DEC-01
04-JAN-02 31-DEC-01
05-JAN-02 31-DEC-01
06-JAN-02 31-DEC-01
07-JAN-02 07-JAN-02
08-JAN-02 07-JAN-02
09-JAN-02 07-JAN-02
10-JAN-02 07-JAN-02
11-JAN-02 07-JAN-02
12-JAN-02 07-JAN-02
13-JAN-02 07-JAN-02
14-JAN-02 14-JAN-02
15-JAN-02 14-JAN-02
16-JAN-02 14-JAN-02
17-JAN-02 14-JAN-02
18-JAN-02 14-JAN-02
19-JAN-02 14-JAN-02
20-JAN-02 14-JAN-02
21-JAN-02 21-JAN-02
22-JAN-02 21-JAN-02
23-JAN-02 21-JAN-02
24-JAN-02 21-JAN-02
25-JAN-02 21-JAN-02
26-JAN-02 21-JAN-02
27-JAN-02 21-JAN-02
28-JAN-02 28-JAN-02
29-JAN-02 28-JAN-02
29 rows selected.
'iw' might do it for you...
rchin, December 16, 2002 - 2:09 pm UTC
My requirement is that Friday marks end of a week (so 1/4/02 should be end of week 1) so
I hacked around and comeup with something like below...
Do you think this is gonna hold up or is there some session
variable I can so I can use 'IW' or 'WW' ?
FUNCTION weeknumber(p_Date IN VARCHAR2) RETURN INTEGER
IS
i INTEGER;
p_year VARCHAR2(4) := to_char(to_date(p_Date), 'yyyy') ;
BEGIN
i := round((round(next_day(p_d1,'FRIDAY') - next_day(to_date('1-Jan-'|| p_year),'FRIDAY')) / 365) * 52) + 1;
RETURN i;
END;
December 16, 2002 - 3:09 pm UTC
why not just
ops$tkyte@ORA817DEV.US.ORACLE.COM> select dt, to_char( dt+2, 'iw' )
2 from ( select to_date('01/01/2002','dd/mm/yyyy')+rownum-1 dt
from all_objects where rownum < 30 )
3 /
DT TO
--------- --
01-JAN-02 01
02-JAN-02 01
03-JAN-02 01
04-JAN-02 01
05-JAN-02 02
06-JAN-02 02
07-JAN-02 02
08-JAN-02 02
09-JAN-02 02
10-JAN-02 02
11-JAN-02 02
12-JAN-02 03
13-JAN-02 03
14-JAN-02 03
15-JAN-02 03
16-JAN-02 03
17-JAN-02 03
18-JAN-02 03
19-JAN-02 04
20-JAN-02 04
21-JAN-02 04
22-JAN-02 04
23-JAN-02 04
24-JAN-02 04
25-JAN-02 04
26-JAN-02 05
27-JAN-02 05
28-JAN-02 05
29-JAN-02 05
29 rows selected.
instead -- don't go the plsql route unless you have to and I don't think you do.
Pls Repeat it....
robert, December 16, 2002 - 3:41 pm UTC
Please do us all a favor and repeat your famous mantra
in BIG CAPITAL fonts in your new book...
Warning ! suggestion coming up:
(or perhaps even put it on disk in an audio file in a soothing hypnotic voice so we can play it over and over....)
using Oracle 8i
Sivababu, December 18, 2002 - 8:29 am UTC
Hi TOM,
It is great. But how can i use with oracle 8.1.7?.
Waiting for your reply.
regards,
Siva
December 18, 2002 - 12:39 pm UTC
as i said -- use nvl instead of coalesce
ops$tkyte@ORA817DEV.US.ORACLE.COM> select groupnm,
2 nvl(rn1,nvl(rn2,rn3)) rn,
3 max(ev1) "Jan", max(ev2) "Feb", max(ev3) "Mar"
4 from ( select groupnm,
5 decode( month, '01-jan-2002', rn, null ) rn1,
6 decode( month, '01-jan-2002', event, null ) ev1,
7 decode( month, '01-feb-2002', rn, null ) rn2,
8 decode( month, '01-feb-2002', event, null ) ev2,
9 decode( month, '01-mar-2002', rn, null ) rn3,
10 decode( month, '01-mar-2002', event, null ) ev3
11 from ( select event, month, groupnm,
12 row_number() over ( partition by month, groupnm
13 order by event ) rn
14 from t
15 ) t
16 ) t
17 group by groupnm, nvl(rn1,nvl(rn2,rn3))
18 /
G RN Jan Feb Mar
- ---------- --- --- ---
A 1 X XX XXX
A 2 Y YY YYY
A 3 abc
B 1 Z ZZ ZZZ
B 2 xxx
C 1 A AA AAA
6 rows selected.
pivot question
vf, March 13, 2003 - 10:36 am UTC
Just simplifying my problem with a sample table:
I have a table1 with col1 and col2.
col1 col2
------ ------
1 a
1 b
2 e
2 f
2 g
3 x
3 y
I need the out put as :
col1 col
---- ----
1 a,b
2 e,f,g
3 x,y
For using a pivot query and concatenate the pivoted columns i need to know beforehand the max number of unique col1 values(meaning max 3 rows for value '2'). Then i can use:
select col1, max(decode(r,1,col2))||','||max(decode(r,2,col2))||','||max(decode(r,3,col2)) col from
(select col1,col2, row_number() over (partition by col1 order by col1) as r from table1)
But the problem is that i do not know the max no of col1 values.(I am getting this result 'select col1,col2 from table1' at runtime and the result can vary as per the where clause).
Is ther any way to acheive this thru SQL only?
I can do this with a procedure which make a dynamic query (or thru someother plsql means) but is ther a way to do this with SQL only?
March 14, 2003 - 5:23 pm UTC
in 8i - no
in 9i - yes, search for stragg on this site.
vf, March 13, 2003 - 10:39 am UTC
oracle 8i is my environment for teh above question
Pivot
Ratan, January 23, 2004 - 10:17 am UTC
Hi Tom,
I have data in table as follows.
SQL> select * from t1;
A B
---------- ----------
A ABC
A PQR
A XYZ
1 123
1 234
1 345
I am trying for following output.
A 1
ABC 123
XYZ 234
PQR 345
Please help me to write query to getthe above output.
Thanks in advance.
Ratan.
January 23, 2004 - 6:27 pm UTC
well, hmmm -- i guess you'd have to clue us into the "logic" here since it is definitely not "obvious"
about last post: Pivot
Marcio, March 29, 2004 - 12:28 pm UTC
About last post (Ratan - Pivot)
would be this?
ops$t_mp00@MRP920> select * from t;
A B
- ---
A ABC
A PQR
A XYZ
1 123
1 234
1 345
6 rows selected.
ops$t_mp00@MRP920>
ops$t_mp00@MRP920> column rn noprint
ops$t_mp00@MRP920>
ops$t_mp00@MRP920> select rn, max("A") "A", max("1") "1"
2 from (
3 select row_number() over (partition by a order by a) rn,
4 decode(a, 'A', b, null) "A",
5 decode(a, '1', b, null) "1"
6 from t
7 )
8 group by rn
9 /
A 1
--- ---
ABC 123
PQR 234
XYZ 345
3 rows selected.
Pivots are great!
Michael Wexler, April 05, 2004 - 12:56 pm UTC
MS SQL ("Yukon") will have a PIVOT command built into the next version of SQL Server.
</code>
http://builder.com.com/5100-6388-5161769.html?tag=sc <code>
I am sure Oracle will include one later on. Til then, Tom's pivot tips are one of the few things keeping me sane when trying to do advanced analysis in Oracle.
April 05, 2004 - 5:09 pm UTC
hmm, looks HARDER than the max() trick doesn't it? (to me, yes, yes it does)
doesn't seem to add anything?
I mean, how is:
SELECT *
FROM SVGProperties AS MyDerivedTable
PIVOT (
MAX(SVGPropertyValue)
FOR SVGProperty IN([Line0001], [Line0002], [Line0003], [Line0004], [Line0005]
) AS MyPIVOT
WHERE ImageID in (1,2,3,6)
any easier, more functional or better than the SQL:
SELECT
ImageID,
MAX(CASE WHEN SVGProperty='Line0001' THEN SVGPropertyValue END) as Line0001,
MAX(CASE WHEN SVGProperty='Line0002' THEN SVGPropertyValue END) as Line0002,
MAX(CASE WHEN SVGProperty='Line0003' THEN SVGPropertyValue END) as Line0003,
MAX(CASE WHEN SVGProperty='Line0004' THEN SVGPropertyValue END) as Line0004,
MAX(CASE WHEN SVGProperty='Line0005' THEN SVGPropertyValue END) as Line0005
FROM SVGProperties
WHERE ImageID IN(1,2,3,6)
GROUP BY ImageID
More proprietary perhaps, but not any more efficient, easier...
coalesce -- but rows
john, April 23, 2004 - 12:44 am UTC
Tom,
is there a way or function to coalesce the rows, for example:
Tom,
can we coalesce rows? for example
id date1 date2 date3
1 1-jan-2003
1 8-jan-2003 8-jan-2003
1 15-jan-2003 15-jan-2003
1 23-jan-2003
2 1-jan-2003
2 8-jan-2003 8-jan-2003
2 15-jan-2003 15-jan-2003
2 23-jan-2003
i need to coalesce rows of each column date1, date2, date3 for each id, like below:
1 1-jan-2003 8-jan-2003 8-jan-2003
1 15-jan-2003 23-jan-2003 15-jan-2003
2 1-jan-2003 8-jan-2003 15-jan-2003
2 8-jan-2003 23-jan-2003
2 15-jan-2003
thanks a million
April 23, 2004 - 11:41 am UTC
ops$tkyte@ORA9IR2> select * from t;
ID D1 D2 D3
---------- --------- --------- ---------
1 01-JAN-03
1 08-JAN-03 08-JAN-03
1 15-JAN-03 15-JAN-03
1 23-JAN-03
2 01-JAN-03
2 08-JAN-03 08-JAN-03
2 15-JAN-03 15-JAN-03
2 23-JAN-03
8 rows selected.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> with q
2 as
3 (select id,
4 d1, decode(d1,null,to_number(null),row_number() over (partition by id order by d1 nulls last)) r1,
5 d2, decode(d2,null,to_number(null),row_number() over (partition by id order by d2 nulls last)) r2,
6 d3, decode(d3,null,to_number(null),row_number() over (partition by id order by d3 nulls last)) r3
7 from t
8 )
9 select coalesce(x.id,q3.id) id,
10 coalesce(x.r, q3.r3) r,
11 x.d1, x.d2, q3.d3
12 from (
13 select coalesce(q1.id,q2.id) id, coalesce(q1.r1,q2.r2) r, q1.d1, q2.d2
14 from (select * from q where r1 is not null ) q1 full outer join
15 (select * from q where r2 is not null ) q2 on (q1.id = q2.id and q1.r1 = q2.r2)
16 ) X full outer join
17 (select * from q where r3 is not null ) q3 on (x.id = q3.id and x.r = q3.r3)
18 /
ID R D1 D2 D3
---------- ---------- --------- --------- ---------
1 1 01-JAN-03 08-JAN-03 08-JAN-03
1 2 15-JAN-03 23-JAN-03 15-JAN-03
2 1 01-JAN-03 08-JAN-03 15-JAN-03
2 2 08-JAN-03 23-JAN-03
2 3 15-JAN-03
"pretty" -- maybe, maybe not...
In general though, i doubt there is a good solution for you as you probably intend for:
1 8-jan-2003 8-jan-2003
that pair to "stay together" but it might -- it might not. if you just want to treat the 3 columns independently, this work.
coalesche -- but rows
John, April 23, 2004 - 12:45 am UTC
using Oracle 9.2
sql soln for pivot Db ver 9.2
A reader, August 01, 2004 - 6:48 am UTC
(Scripts are below at the end)
Hi,
I am trying to compare for an id and type their amt with respect to date ranges.So I create artificial dates
so that I get the exact dates for when the price change and also the valid ranges where it falls into given range by a_start_date ,a_end_date,b_start_date,b_end_Date.
min start date and max start date gives the valid dates for that price may be same for type A and B or different.
My problem is when there is an overlap such that there are multiple start dates for a particualr type the max decode function only takes the max price value (7/11/2003 price 64 FOR TYPE b )in the decode and does not give the other values ( 62 FOR TYPE B) IN The comparison.
So i tried using a min also that gave reuslt 2.
Do you have a better alternative SQL soln.
I still think the union of max and min may not be the correct thing to do
I also tried without the max in the decode. didnt work
Actually I need differences for all ranges whether it overlaps or not to be shown in the format i show with
type A results side by side where ever applicable.
Is the min union max soln correct way to do it ?
Any suggestions on how would you advise todo this ?(may be using some analytics )
SELECT * FROM T
ID TYPE START_DATE END_DATE AMT
--------------- ----- --------------------- --------------------- --------------
01 A 7/11/2003 10/1/2003 64
01 A 1/3/2002 6/30/2002 62
01 B 7/11/2003 10/2/2003 64
01 B 7/11/2003 8/12/2003 62
01 B 8/13/2003 10/2/2003 62
01 B 1/3/2002 6/30/2002 78
6 rows selected
SELECT id,
MIN (start_dt),
MAX (start_dt),
a_amt,
b_amt,
a_start_dt,
a_end_dt,
b_start_dt,
b_end_dt
FROM (SELECT id,
start_date
+ r start_dt,
MAX(DECODE (TYPE, 'A', amt)) a_amt,
MAX(DECODE (TYPE, 'B', amt)) b_amt,
MAX(DECODE (TYPE, 'A', start_date)) a_start_dt,
MAX(DECODE (TYPE, 'A', end_date)) a_end_dt,
MAX(DECODE (TYPE, 'B', start_date)) b_start_dt,
MAX(DECODE (TYPE, 'B', end_date)) b_end_dt
FROM (SELECT *
FROM t),
(SELECT ROWNUM
- 1 r
FROM all_objects)
WHERE r < ( end_date
- start_date
+ 1
)
GROUP BY id, start_date
+ r)
GROUP BY id, a_amt, b_amt, a_start_dt, a_end_dt, b_start_dt, b_end_dt
ORDER BY 2,3
RRESULT 1:
D MIN(START_DT) MAX(START_DT) A_AMT B_AMT A_START_DT A_END_DT B_START_DT B_END_DT
--------------- --------------------- --------------------- --------------------------------------- --------------------------------------- --------------------- --------------------- --------------------- ---------------------
01 1/3/2002 6/30/2002 62 78 1/3/2002 6/30/2002 1/3/2002 6/30/2002
01 7/11/2003 8/12/2003 64 64 7/11/2003 10/1/2003 7/11/2003 10/2/2003
01 8/13/2003 10/1/2003 64 64 7/11/2003 10/1/2003 8/13/2003 10/2/2003
01 10/2/2003 10/2/2003 64 8/13/2003 10/2/2003
4 rows selected
SELECT id,
MIN (start_dt),
MAX (start_dt),
a_amt,
b_amt,
a_start_dt,
a_end_dt,
b_start_dt,
b_end_dt
FROM (SELECT id,
start_date
+ r start_dt,
MAX (DECODE (TYPE, 'A', amt)) a_amt,
MAX (DECODE (TYPE, 'B', amt)) b_amt,
MAX (DECODE (TYPE, 'A', start_date)) a_start_dt,
MAX (DECODE (TYPE, 'A', end_date)) a_end_dt,
MAX (DECODE (TYPE, 'B', start_date)) b_start_dt,
MAX (DECODE (TYPE, 'B', end_date)) b_end_dt
FROM (SELECT *
FROM t),
(SELECT ROWNUM
- 1 r
FROM all_objects)
WHERE r < ( end_date
- start_date
+ 1
)
GROUP BY id, start_date
+ r
UNION
SELECT id,
start_date
+ r start_dt,
MIN (DECODE (TYPE, 'A', amt)) a_amt,
MIN (DECODE (TYPE, 'B', amt)) b_amt,
MIN (DECODE (TYPE, 'A', start_date)) a_start_dt,
MIN (DECODE (TYPE, 'A', end_date)) a_end_dt,
MIN (DECODE (TYPE, 'B', start_date)) b_start_dt,
MIN (DECODE (TYPE, 'B', end_date)) b_end_dt
FROM (SELECT *
FROM t),
(SELECT ROWNUM
- 1 r
FROM all_objects)
WHERE r < ( end_date
- start_date
+ 1
)
GROUP BY id, start_date
+ r)
GROUP BY id, a_amt, b_amt, a_start_dt, a_end_dt, b_start_dt, b_end_dt
ORDER BY 2, 3
RESULT 2 :
ID MIN(START_DT) MAX(START_DT) A_AMT B_AMT A_START_DT A_END_DT B_START_DT B_END_DT
--------------- --------------------- --------------------- --------------------------------------- --------------------------------------- --------------------- --------------------- --------------------- ---------------------
01 1/3/2002 6/30/2002 62 78 1/3/2002 6/30/2002 1/3/2002 6/30/2002
01 7/11/2003 8/12/2003 64 62 7/11/2003 10/1/2003 7/11/2003 8/12/2003
01 7/11/2003 8/12/2003 64 64 7/11/2003 10/1/2003 7/11/2003 10/2/2003
01 8/13/2003 10/1/2003 64 62 7/11/2003 10/1/2003 7/11/2003 10/2/2003
01 8/13/2003 10/1/2003 64 64 7/11/2003 10/1/2003 8/13/2003 10/2/2003
01 10/2/2003 10/2/2003 62 7/11/2003 10/2/2003
01 10/2/2003 10/2/2003 64 8/13/2003 10/2/2003
7 rows selected
sCRIPTS
CREATE TABLE T (
ID VARCHAR2 (15) NOT NULL,
TYPE VARCHAR2 (5),
START_DATE DATE NOT NULL,
END_DATE DATE,
AMT NUMBER (13))
INSERT INTO T ( ID, TYPE, START_DATE, END_DATE, AMT ) VALUES (
'01', 'A', TO_Date( '07/11/2003 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '10/01/2003 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, '64');
INSERT INTO T ( ID, TYPE, START_DATE, END_DATE, AMT ) VALUES (
'01', 'A', TO_Date( '01/03/2002 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '06/30/2002 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, '62');
INSERT INTO T ( ID, TYPE, START_DATE, END_DATE, AMT ) VALUES (
'01', 'B', TO_Date( '07/11/2003 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '10/02/2003 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, '64');
INSERT INTO T ( ID, TYPE, START_DATE, END_DATE, AMT ) VALUES (
'01', 'B', TO_Date( '07/11/2003 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '08/12/2003 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, '62');
INSERT INTO T ( ID, TYPE, START_DATE, END_DATE, AMT ) VALUES (
'01', 'B', TO_Date( '08/13/2003 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '10/02/2003 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, '62');
INSERT INTO T ( ID, TYPE, START_DATE, END_DATE, AMT ) VALUES (
'01', 'B', TO_Date( '01/03/2002 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '06/30/2002 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, '78');
COMMIT;
August 01, 2004 - 11:01 am UTC
My problem is when there is an overlap such that there are multiple start
dates for a particualr type the max decode function only takes the max price
value (7/11/2003 price 64 FOR TYPE b )in the decode and does not give the other
values ( 62 FOR TYPE B) IN The comparison.
explain that in more detail. when there are overlaps -- which price is the correct price? how can you tell?
this looks like simple lag/lead analysis -- i don't think you want or need to explode the rows out.
When overlap
A reader, August 01, 2004 - 12:57 pm UTC
For the overlap for type B
start date end date amt
7/11/2003 10/2/2003 64
7/11/2003 8/12/2003 62
For type B I also need the row 7/11/2003 8/12/2003 62 in the comparison.This does not come up in result 1
But if you do a min(decode) it comes up in result 2.
wrt"explain that in more detail. when there are overlaps -- which price is the
correct price? how can you tell? "
Need to compare with what the actual range and price is rather than the max(decode) or min (decode) when there is overlap. When there is no overlap the max(decode) works fine as there is only one row for each type date range.
This is just to show the discrepancy so all prices and ranges are to be included.
wrt"this looks like simple lag/lead analysis -- i don't think you want or need to
explode the rows out. "
Can you please give an example of lag and lead.I exploded rows to get the min(start_date) max(start_date) which tells us within the
given range of type A and type B the actual date range for which the price was valid thru the min(start) and max(start) columns( please see result 1).
The last row of result 1 shows that
on 10/2/2000 type B had price of 64 and type A did not have any.
ID MIN(START_DT) MAX(START_DT) A_AMT B_AMT A_START_DT A_END_DT B_START_DT B_END_DT
--------------- --------------------- --------------------- --------------------------------------- --------------------------------------- --------------------- --------------------- --------------------- ---------------------
01 10/2/2003 10/2/2003 64 8/13/2003 10/2/2003
August 01, 2004 - 3:28 pm UTC
can you tell me the analysis you are trying to perform here? what are the outputs exactly. I don't know what you mean by "an actual range" -- what if there are 40 overlapping ranges.
the analysis
A reader, August 01, 2004 - 6:32 pm UTC
For every price ,data range for an ID and type A I need to club the corresponding price, date range for type B.
Also I need to show how the range for type A fits within sets of ranges of type B hence result 1 shows that.
It also has the min start and max start date columns to show the dates for which the price is valid for both types.
for example say id 01 has
for type A say we have start date ,end date, amt as
7/11/2003,10/1/2003,64
and values for type B are
ID TYPE START_DATE END_DATE AMT
--------------- ----- --------------------- --------------------- --------------
01 B 7/11/2003 10/2/2003 65
01 B 7/11/2003 8/12/2003 62
01 B 8/13/2003 10/2/2003 62
I need the output as.
ID MIN(START_DT) MAX(START_DT) A_AMT B_AMT A_START_DT A_END_DT B_START_DT B_END_DT
--------------- --------------------- --------------------- --------------------------------------- --------------------------------------- --------------------- --------------------- --------------------- ---------------------
01 7/11/2003 8/12/2003 64 62 7/11/2003 10/1/2003 7/11/2003 8/12/2003
01 7/11/2003 8/12/2003 64 64 7/11/2003 10/1/2003 7/11/2003 10/2/2003
01 8/13/2003 10/1/2003 64 62 7/11/2003 10/1/2003 7/11/2003 10/2/2003
01 8/13/2003 10/1/2003 64 64 7/11/2003 10/1/2003 8/13/2003 10/2/2003
01 10/2/2003 10/2/2003 62 7/11/2003 10/2/2003
01 10/2/2003 10/2/2003 64 8/13/2003 10/2/2003
Sorry the data get wrapped.
so for the range of type A 7/11/2003 -- 10/1/2003
I show date ranges of type B which fit in for type A
hence you see the a_start_date and A_end_Date
repeated for the values of b_start_date and b_end_date.Pls see the output above.
"Type A will never have overlapping date ranges wrt to itself"
Type B may have overlapping date ranges wrt to itself ( when compared wrt only data sets for type B)
The data wont have 40 overlapping at the most upto 5 if it exists
So the analysis is just to compare date ranges and price of types A with type B for a given ID.
And show how the two types fit together if at all.
{ a sample of output which has no overlapping dates for type b
id MIN_start_dt MAX_start_dt A_amt b_amt a_start_date a_end_Date b_start_date b_end_date
--------------- ---------------------------------------- --------------------- --------------------- --------------------------------------- --------------------------------------- ---------------------
01 1/11/2000 1/7/2001 115 115 1/11/2000 1/7/2001 1/11/2000 1/7/2001
01 1/8/2001 1/1/2002 119 119 1/8/2001 1/1/2002 1/8/2001 1/2/2002
01 1/2/2002 1/2/2002 122 119 1/2/2002 1/1/2003 1/8/2001 1/2/2002
01 1/2/2003 7/9/2003 122 122 1/2/2003 7/9/2003 1/3/2002 1/1/2004
}
August 02, 2004 - 7:31 am UTC
sorry -- just not understanding the output. if you unwrapped that first wide result -- you have
01 7/11/2003 8/12/2003 64 62 7/11/2003 10/1/2003 7/11/2003 8/12/2003
01 7/11/2003 8/12/2003 64 64 7/11/2003 10/1/2003 7/11/2003 10/2/2003
01 8/13/2003 10/1/2003 64 62 7/11/2003 10/1/2003 7/11/2003 10/2/2003
01 8/13/2003 10/1/2003 64 64 7/11/2003 10/1/2003 8/13/2003 10/2/2003
01 10/2/2003 10/2/2003 62 7/11/2003 10/2/2003
01 10/2/2003 10/2/2003 64 8/13/2003 10/2/2003
as b has values 62 and 65, but no 64's -- not getting it. if you have a query that is working for you though and going as fast as you need -- stick with it I guess.
PIVOT
A reader, August 02, 2004 - 12:40 am UTC
Come on Tom, Oracle can introduce proprietary extensions like MERGE, GROUPING, CUBE, etc to its SQL toolchest, but when MS SQL Server adds PIVOT(), you complain
"any easier, more functional or better than the SQL"
"More proprietary perhaps, but not any more efficient, easier..."
Lets be fair, give credit where credit is due!
August 02, 2004 - 7:48 am UTC
Merge -- submitted to ansi.
grouping, cube, already part of it.
we have very few proprietary extensions to sql and they are generally the older legacy features -- from the time before ansi sql, mssqlserver and so on.
but did you read about this pivot feature? my gripe was not about the proprietary nature in as much as "this doesn't make it any easier at all"
quote:
how is:
SELECT *
FROM SVGProperties AS MyDerivedTable
PIVOT (
MAX(SVGPropertyValue)
FOR SVGProperty IN([Line0001], [Line0002], [Line0003], [Line0004],
[Line0005]
) AS MyPIVOT
WHERE ImageID in (1,2,3,6)
any easier, more functional or better than the SQL:
SELECT
ImageID,
MAX(CASE WHEN SVGProperty='Line0001' THEN SVGPropertyValue END) as Line0001,
MAX(CASE WHEN SVGProperty='Line0002' THEN SVGPropertyValue END) as Line0002,
MAX(CASE WHEN SVGProperty='Line0003' THEN SVGPropertyValue END) as Line0003,
MAX(CASE WHEN SVGProperty='Line0004' THEN SVGPropertyValue END) as Line0004,
MAX(CASE WHEN SVGProperty='Line0005' THEN SVGPropertyValue END) as Line0005
FROM SVGProperties
WHERE ImageID IN(1,2,3,6)
GROUP BY ImageID
Using ANSI SQL -- every database already has this "pivot" feature today.
Sorry typo 65 should be 64
A reader, August 02, 2004 - 9:08 am UTC
Sorry for the typo.Value for B should be 64 and 62 and not 65. Its a typo.
and values for type B are
ID TYPE START_DATE END_DATE AMT
--------------- ----- --------------------- --------------------- --------------
01 B 7/11/2003 10/2/2003 64
01 B 7/11/2003 8/12/2003 62
01 B 8/13/2003 10/2/2003 62 .
So the analysis is just to compare date ranges and price of types A with type B
for a given ID.
And show how the two types fit together if at all.
I am having problems with the query I shown you before when there are overlaps because of max(decode) function.
as max will take the max start date ,max end date ,max price.
the output
01 7/11/2003 8/12/2003 64 62 7/11/2003 10/1/2003 7/11/2003 8/12/2003
01 7/11/2003 8/12/2003 64 64 7/11/2003 10/1/2003 7/11/2003 10/2/2003
01 8/13/2003 10/1/2003 64 62 7/11/2003 10/1/2003 7/11/2003 10/2/2003
01 8/13/2003 10/1/2003 64 64 7/11/2003 10/1/2003 8/13/2003 10/2/2003
01 10/2/2003 10/2/2003 62 7/11/2003 10/2/2003
01 10/2/2003 10/2/2003 64 8/13/2003 10/2/2003
I got is what is required I got thru manipulation of max and min decode as I have listed earlier.
I am not convinced it to be the correct solution.(max will take only max values , min will take min values then it may discard values in between max and min )
Ok in general is it possible to pivot all values for type
B for columns start date, end date ,price when grouped by
the start date and not just the max of it.(max(decode) takes only max.. I want all values)
Any suggestions ,clues on the fix.
Thanx for your help Sir
August 02, 2004 - 10:20 am UTC
sorry, having a truly hard time envisioning this, I'm not sure we'll come to a conclusion.
I don't get why 4 rows with A and B values. I see three B rows. Each overlaps with the single A row. I don't get the 4th row
ID MIN(B.DT) MAX(B.DT) AMT AMT START_DATE END_DATE START_DATE END_DATE
-- ---------- ---------- --- --- ---------- ---------- ---------- ----------
01 07/11/2003 08/12/2003 64 62 07/11/2003 10/01/2003 07/11/2003 08/12/2003
01 08/13/2003 10/01/2003 64 62 07/11/2003 10/01/2003 08/13/2003 10/02/2003
01 07/11/2003 10/01/2003 64 64 07/11/2003 10/01/2003 07/11/2003 10/02/2003
01 10/02/2003 10/02/2003 62 08/13/2003 10/02/2003
01 10/02/2003 10/02/2003 64 07/11/2003 10/02/2003
I can "see", yours -- just not getting it.
4th Row because of explode
A reader, August 02, 2004 - 10:46 am UTC
Thanx Sir for your patience.
The 4th and 5th row comes as a result of exploding the date ranges (using all_objects query(SELECT ROWNUM
- 1 r
FROM all_objects)
WHERE r < ( end_date
- start_date
+ 1
)
).
That shows that on 10/2/2003 there was no entry for type A
as its range was from 07/11/2003 to "10/01/2003".
the min(startdt) and max(startdt) gives us that value.
id min(startdt) max(startdt)
01 7/11/2003 8/12/2003
01 7/11/2003 8/12/2003
01 8/13/2003 10/1/2003
01 8/13/2003 10/1/2003
01 10/2/2003 10/2/2003
01 10/2/2003 10/2/2003
If you see above the min and max dates are continuous from
min to max when you see the current and prior values
(7/11 -- 8/12 : 8/13 -- 10/1 : 10/2 --10/2 )
And on 10/2/2003 the AMT for type B is 62 as well as 64.
Hence you get 4th and 5th rows.
01 10/02/2003 10/02/2003 62 08/13/2003 10/02/2003
01 10/02/2003 10/02/2003 64 07/11/2003 10/02/2003
From the original set of B data:
01 B 7/11/2003 10/2/2003 64
01 B 8/13/2003 10/2/2003 62 .
I hope you understood. I apologize for the confusions.
If I am not able to make you understand still.
Ok a general question on pivot.
How to pivot all values .
Is it possible to pivot all values for type
B for columns start date, end date ,price when grouped by
the start date and not just the max of it.(max(decode) takes only max.. I want
all values). I was reading something on coalese.
August 02, 2004 - 11:01 am UTC
I understand the last 2 rows. YOU HAVE 6 rows -- rows 5 and 6, I understand. rows 1..4 I do not.
You wrote:
the output
01 7/11/2003 8/12/2003 64 62 7/11/2003 10/1/2003 7/11/2003 8/12/2003
01 7/11/2003 8/12/2003 64 64 7/11/2003 10/1/2003 7/11/2003 10/2/2003
01 8/13/2003 10/1/2003 64 62 7/11/2003 10/1/2003 7/11/2003 10/2/2003
01 8/13/2003 10/1/2003 64 64 7/11/2003 10/1/2003 8/13/2003 10/2/2003
01 10/2/2003 10/2/2003 62 7/11/2003 10/2/2003
01 10/2/2003 10/2/2003 64 8/13/2003 10/2/2003
rows 5 and 6, got it.
rows 1, 2, 3, 4 -- don't got it.
Pls see the required corrected output.
A reader, August 02, 2004 - 12:08 pm UTC
Thanx Sir for looking into this.I have corrected my output.
You are correct as always.I apologize for the confusion but it helped me correct the output.
01 7/11/2003 8/12/2003 64 64 7/11/2003 10/1/2003 7/11/2003 10/2/2003
01 7/11/2003 8/12/2003 64 62 7/11/2003 10/1/2003 7/11/2003 8/12/2003
01 8/13/2003 10/1/2003 64 62 7/11/2003 10/1/2003 8/13/2003 10/2/2003
01 8/13/2003 10/1/2003 64 64 7/11/2003 10/1/2003 7/11/2003 10/2/2003
01 10/2/2003 10/2/2003 64 7/11/2003 10/2/2003
01 10/2/2003 10/2/2003 62 8/13/2003 10/2/2003
first row is for type B's range :
01 B 7/11/2003 10/2/2003 64
2nd and third row are for typw B's range:
01 B 7/11/2003 8/12/2003 62
01 B 8/13/2003 10/2/2003 62
4th row is coming because of range for type B:
01 B 7/11/2003 10/2/2003 64
The min start dt - max start dt is 8/13/2003 10/1/2003
as during this time period the price is also 64 apart from being 62
5th row from range for type B:
01 B 7/11/2003 10/2/2003 64
6th row from range for type B:
01 B 8/13/2003 10/2/2003 62
*******************************
ID TYPE START_DATE END_DATE AMT
--------------- ----- --------------------- --------------------- --------------
01 A 7/11/2003 10/1/2003 64
01 B 7/11/2003 10/2/2003 64
01 B 7/11/2003 8/12/2003 62
01 B 8/13/2003 10/2/2003 62
August 02, 2004 - 12:55 pm UTC
don't get that 4th row at all. sorry. if you have a query that is working for you, i think thats as good as we get. I don't understand that 4th row at all.
sometimes it is just not to be.
Lets put 4th row aside
A reader, August 02, 2004 - 1:31 pm UTC
Ok lets assume I do not want the 4th row.
Now how to get the required output.
for the table with values.
ID TYPE START_DATE END_DATE AMT
--------------- ----- --------------------- --------------------- --------------
01 A 7/11/2003 10/1/2003 64
01 B 7/11/2003 10/2/2003 64
01 B 7/11/2003 8/12/2003 62
01 B 8/13/2003 10/2/2003 62
I need to have:
01 7/11/2003 8/12/2003 64 64 7/11/2003 10/1/2003 7/11/2003 10/2/2003
01 7/11/2003 8/12/2003 64 62 7/11/2003 10/1/2003 7/11/2003 8/12/2003
01 8/13/2003 10/1/2003 64 62 7/11/2003 10/1/2003 8/13/2003 10/2/2003
01 10/2/2003 10/2/2003 64 7/11/2003 10/2/2003
01 10/2/2003 10/2/2003 62 8/13/2003 10/2/2003.
column heading for above are :
id , from , to , a_amt, b_amt, a_start_date, a_end_date, b_start_date, b_end_date.
The following query works for non overlapping dates ( for type b ,type a will never have overlaping dates) but fails for the above data set of table t.
SELECT id,
MIN (start_dt),
MAX (start_dt),
a_amt,
b_amt,
a_start_dt,
a_end_dt,
b_start_dt,
b_end_dt
FROM (SELECT id,
start_date
+ r start_dt,
MAX(DECODE (TYPE, 'A', amt)) a_amt,
MAX(DECODE (TYPE, 'B', amt)) b_amt,
MAX(DECODE (TYPE, 'A', start_date)) a_start_dt,
MAX(DECODE (TYPE, 'A', end_date)) a_end_dt,
MAX(DECODE (TYPE, 'B', start_date)) b_start_dt,
MAX(DECODE (TYPE, 'B', end_date)) b_end_dt
FROM (SELECT *
FROM vt),
(SELECT ROWNUM
- 1 r
FROM all_objects)
WHERE r < ( end_date
- start_date
+ 1
)
GROUP BY id, start_date
+ r)
GROUP BY id, a_amt, b_amt, a_start_dt, a_end_dt, b_start_dt, b_end_dt
ORDER BY 2,3
Any suggestions now.
( scripts given earlier )
August 02, 2004 - 2:45 pm UTC
this is what I've come up with so far -- the 500 could be replaced with a query to find the max number of days between
ops$tkyte@ORA9IR2> with rs
2 as
3 ( select rownum-1 r
4 from all_objects
5 where rownum <= 500
6 )
7 select b.id,
8 min(b.dt) mindt, max(b.dt) maxdt, a.amt a, b.amt b,
9 a.start_date a_start, a.end_date a_end,
10 b.start_date b_start, b.end_date b_end
11 from
12 (
13 select id, start_date+r dt, amt, start_date, end_date
14 from (select * from t where type = 'A' ), rs
15 where r <= end_date-start_date
16 ) a,
17 (
18 select id, start_date+r dt, amt, start_date, end_date
19 from (select * from t where type = 'B' ), rs
20 where r <= end_date-start_date
21 ) b
22 where b.dt = a.dt(+)
23 group by b.id, b.amt, a.amt, a.start_date, a.end_date, b.start_date, b.end_date
24 order by 3
25 /
ID MINDT MAXDT A B A_START A_END B_START B_END
-- ---------- ---------- --- --- ---------- ---------- ---------- ----------
01 07/11/2003 08/12/2003 64 62 07/11/2003 10/01/2003 07/11/2003 08/12/2003
01 08/13/2003 10/01/2003 64 62 07/11/2003 10/01/2003 08/13/2003 10/02/2003
01 07/11/2003 10/01/2003 64 64 07/11/2003 10/01/2003 07/11/2003 10/02/2003
01 10/02/2003 10/02/2003 62 08/13/2003 10/02/2003
01 10/02/2003 10/02/2003 64 07/11/2003 10/02/2003
IT should be select * from t and not select * from vt
A reader, August 02, 2004 - 1:34 pm UTC
Pivoting
sachin, August 03, 2004 - 3:35 am UTC
i found your response most useful because you gave it stepwise that really helps to understand the steps to be followed while solving the problem
Amazing
khangarots, April 22, 2005 - 10:15 am UTC
Thanks TOM for putting lots of efforts for the oracle community.
Peter, July 21, 2005 - 9:01 am UTC
Hello Tom,
this is almost what i need to produce our report. Is it possible to add hierarchies with subtotals? (excel like pivot table) for example:
CREATE TABLE t1
(
region VARCHAR2(10),
branch VARCHAR2(10),
p_group VARCHAR2(10),
product VARCHAR2(10),
SALES NUMBER
)
with 2 dimension region and product like:
region1 branch1
branch2
region2 branch3
branch4
p.group1 prod1
prod2
p.group2 prod3
INSERT INTO t1 VALUES('region1','branch1','group1','prod1',1);
INSERT INTO t1 VALUES('region1','branch2','group1','prod2',3);
INSERT INTO t1 VALUES('region1','branch1','group1','prod2',2);
INSERT INTO t1 VALUES('region1','branch1','group2','prod3',2);
INSERT INTO t1 VALUES('region1','branch2','group2','prod3',4);
INSERT INTO t1 VALUES('region2','branch3','group1','prod1',0);
INSERT INTO t1 VALUES('region2','branch4','group1','prod2',3);
is it possible to produce excel like cross table
in SQL or it's more simple and scalable to use OLAP
option in this case?
p.group1 p.group2 sum
prod1 prod2 sum prod3 sum
region1 branch1 1 2 3 2 2 5
branch2 0 3 3 4 4 7
sum 1 5 6 6 6 12
region2 branch3 0 0 0 0 0 0
branch4 0 3 3 0 0 3
sum 0 3 3 0 0 3
sum 1 8 9 6 6 15
Thank you
July 21, 2005 - 4:09 pm UTC
you need to know the number of products/group and the names of the groups themselves to pivot in SQL.
Do you know that or are you expecting to find that out "on the fly"
Peter, July 21, 2005 - 4:57 pm UTC
My hierarchies are flexible and can be modiefied by end users.
They can also add more levels if needed. Tables have structure
with parent->child relationship
create table prod_hier(
id int,
parent int,
desc varchar2(20)
)
for reporting this recursive table is transformed to
create table rpt_prod_hier(
level01_id int,
level01_desc varchar2(20),
level02_id int,
level02_desc varchar2(20),
level03_id int,
level03_desc varchar2(20),
level04_id int,
level04_desc varchar2(20),
level05_id int,
level05_desc varchar2(20),
.
.
.
)
I can have up to 10 levels in the tree
is it possible to create pivoting query on the fly
using recursive or reporting hierarchy tables?
thank you
Peter, July 21, 2005 - 5:03 pm UTC
to clarify my previous post, there are 2 types of tables
1) hierachy (as stated in before)
2) fact tables that have leaf id if for each dimension used and value (amount for example)
so it's
create table sales(
prod_id int,
branch_id int,
amount int)
and i'm finding region and product group like
select * from sales a, rpt_hier_prod b, rpt_hier_reg c
where a.prod_id=b.level_01_id and a.branch_id=c.level_01_id
thanks
July 21, 2005 - 5:50 pm UTC
this'll be the job of a reporting tool (like excel is a reporting/formatting tool)
disco could likely do this easily, but not SQL so much (you'd need some dynamic sql to figure out what the columns are and write new sql based on that)
Expert One on One Oracle
John, July 22, 2005 - 4:14 am UTC
tom,,Can i get the CD for your book Expert One on One Oracle as we can't carry the book every time..
Thx
John
July 22, 2005 - 8:54 am UTC
The signature edition (hardback version) comes with a CD.
The CD is separately orderable from Apress (on their website)
The CD if the old version (the 2001/2003 version) will be included in volume I of the new version in September.
Zain, February 03, 2006 - 4:09 am UTC
Hi Tom,
I have a problem which i hope you can help me with.
I have created a pivot table which has 1 numeric column, with numbers 0 to 365 in it.
create table pivot (x number);
insert into pivot values (0);
insert into pivot values (1);
...
...
insert into pivot values(365);
I have another table proj_time values which as three columns
create table proj_time values (booked date, name varchar2(30), job_no varchar2(10));
insert into proj_time values ('10-JAN-2006', 'JOE', 'JOB001');
insert into proj_time values ('11-JAN-2006', 'JOE', 'JOB001');
insert into proj_time values ('12-JAN-2006', 'JOE', 'JOB001');
insert into proj_time values ('13-JAN-2006', 'JOE', 'JOB001');
insert into proj_time values ('14-JAN-2006', 'JOE', 'JOB001');
insert into proj_time values ('15-JAN-2006', 'JOE', 'JOB001');
insert into proj_time values ('11-JAN-2006', 'SAM', 'JOB001');
insert into proj_time values ('12-JAN-2006', 'SAM', 'JOB001');
insert into proj_time values ('13-JAN-2006', 'SAM', 'JOB001');
insert into proj_time values ('14-JAN-2006', 'SAM', 'JOB002');
insert into proj_time values ('15-JAN-2006', 'SAM', 'JOB002');
insert into proj_time values ('16-JAN-2006', 'SAM', 'JOB002');
insert into proj_time values ('01-FEB-2006', 'JOE', 'JOB003');
insert into proj_time values ('02-FEB-2006', 'JOE', 'JOB003');
insert into proj_time values ('03-FEB-2006', 'JOE', 'JOB003');
insert into proj_time values ('04-FEB-2006', 'JOE', 'JOB003');
insert into proj_time values ('05-FEB-2006', 'JOE', 'JOB003');
insert into proj_time values ('06-FEB-2006', 'JOE', 'JOB003');
insert into proj_time values ('03-FEB-2006', 'JOHN', 'JOB004');
insert into proj_time values ('04-FEB-2006', 'JOHN', 'JOB004');
insert into proj_time values ('05-FEB-2006', 'JOHN', 'JOB004');
insert into proj_time values ('06-FEB-2006', 'JOHN', 'JOB004');
insert into proj_time values ('07-FEB-2006', 'JOHN', 'JOB004');
A SQL script to retrieve the records as follows:
SQL statement
SELECT (TO_DATE('01-Jan-2006','dd-mon-yyyy') + p.x) dates, t.name, t.job_no
FROM pivot p, proj_time t
WHERE (TO_DATE('01-Jan-2006','dd-mon-yyyy') + p.x) BETWEEN TO_DATE('01-Jan-2006','dd-mon-yyyy') AND TO_DATE('28-Feb-2006','dd-mon-yyyy')
and (TO_DATE('01-Jan-2006','dd-mon-yyyy') + p.x) = t.booked (+)
order by 1, 2, 3;
DATES NAME JOB_NO
--------- ------------------------------ ----------
01-JAN-06
02-JAN-06
03-JAN-06
04-JAN-06
05-JAN-06
06-JAN-06
07-JAN-06
08-JAN-06
09-JAN-06
10-JAN-06 JOE JOB001
11-JAN-06 JOE JOB001
11-JAN-06 SAM JOB001
12-JAN-06 JOE JOB001
12-JAN-06 SAM JOB001
13-JAN-06 JOE JOB001
13-JAN-06 SAM JOB001
14-JAN-06 JOE JOB001
14-JAN-06 SAM JOB002
15-JAN-06 JOE JOB001
15-JAN-06 SAM JOB002
16-JAN-06 SAM JOB002
17-JAN-06
18-JAN-06
19-JAN-06
20-JAN-06
21-JAN-06
22-JAN-06
23-JAN-06
24-JAN-06
25-JAN-06
26-JAN-06
27-JAN-06
28-JAN-06
29-JAN-06
30-JAN-06
31-JAN-06
01-FEB-06 JOE JOB003
02-FEB-06 JOE JOB003
03-FEB-06 JOE JOB003
03-FEB-06 JOHN JOB004
04-FEB-06 JOE JOB003
04-FEB-06 JOHN JOB004
05-FEB-06 JOE JOB003
05-FEB-06 JOHN JOB004
06-FEB-06 JOE JOB003
06-FEB-06 JOHN JOB004
07-FEB-06 JOHN JOB004
08-FEB-06
09-FEB-06
10-FEB-06
11-FEB-06
12-FEB-06
13-FEB-06
14-FEB-06
15-FEB-06
16-FEB-06
17-FEB-06
18-FEB-06
19-FEB-06
20-FEB-06
21-FEB-06
22-FEB-06
23-FEB-06
24-FEB-06
25-FEB-06
26-FEB-06
27-FEB-06
28-FEB-06
However, I would like the data retrieved to look as follows:
DATES JOE JOHN SAM
----------- ------------ ---------- ----------
1/1/2006
2/1/2006
3/1/2006
4/1/2006
5/1/2006
6/1/2006
7/1/2006
8/1/2006
9/1/2006
10/1/2006 JOB001
11/1/2006 JOB001 JOB001
12/1/2006 JOB001 JOB001
13/1/2006 JOB001 JOB001
14/1/2006 JOB001 JOB002
15/1/2006 JOB001 JOB002
16/1/2006 JOB002
17/1/2006
18/1/2006
19/1/2006
20/1/2006
21/1/2006
22/1/2006
23/1/2006
24/1/2006
25/1/2006
26/1/2006
27/1/2006
28/1/2006
29/1/2006
30/1/2006
31/1/2006
1/2/2006 JOB003
2/2/2006 JOB003
3/2/2006 JOB003 JOB004
4/2/2006 JOB003 JOB004
5/2/2006 JOB003 JOB004
6/2/2006 JOB003 JOB004
7/2/2006 JOB004
8/2/2006
9/2/2006
10/2/2006
11/2/2006
12/2/2006
13/2/2006
14/2/2006
15/2/2006
16/2/2006
17/2/2006
18/2/2006
19/2/2006
20/2/2006
21/2/2006
22/2/2006
23/2/2006
24/2/2006
25/2/2006
26/2/2006
27/2/2006
28/2/2006
I will be using the output to schedule new jobs with available people who are not already booked.
Could you please help.
Regards,
zain
The "coalesce" function in your answer for the original posted question is not required.
Frank Zhou, March 03, 2006 - 4:58 pm UTC
Tom,
The "coalesce" function in your answer for the original question is not required.
( instead of using ---> group by groupnm, coalesce( rn1, rn2, rn3 )
we can just using --> group by groupnm, rn )
The following are the test results.
Thanks,
Frank
SQL> SELECT groupnm,
2 CASE WHEN MONTH = TO_DATE('1/1/2002', 'MM/DD/YYYY') THEN MONTH END jan,
3 CASE WHEN MONTH = TO_DATE('2/1/2002', 'MM/DD/YYYY') THEN MONTH END feb,
4 CASE WHEN MONTH = TO_DATE('3/1/2002', 'MM/DD/YYYY') THEN MONTH END mar ,
event , rn
5 FROM
6 (
7 SELECT groupnm, MONTH, event,
8 row_number( ) over (PARTITION BY groupnm, MONTH ORDER BY Event) rn
9 FROM T
10 ) ;
GRO JAN FEB MAR EVE RN
--- --------- --------- --------- --- ----------
A 01-JAN-02 X 1
A 01-JAN-02 Y 2
A 01-JAN-02 abc 3
A 01-FEB-02 XX 1
A 01-FEB-02 YY 2
A 01-MAR-02 XXX 1
A 01-MAR-02 YYY 2
B 01-JAN-02 Z 1
B 01-FEB-02 ZZ 1
B 01-MAR-02 ZZZ 1
B 01-MAR-02 xxx 2
C 01-JAN-02 A 1
C 01-FEB-02 AA 1
C 01-MAR-02 AAA 1
14 rows selected.
SQL> SELECT groupnm, rn, MAX(jan) Jan, MAX(feb) Feb, MAX(mar) Mar
2 FROM
3 (
4 SELECT groupnm,
5 CASE WHEN MONTH = TO_DATE('1/1/2002', 'MM/DD/YYYY') THEN event END jan,
6 CASE WHEN MONTH = TO_DATE('2/1/2002', 'MM/DD/YYYY') THEN event END feb,
7 CASE WHEN MONTH = TO_DATE('3/1/2002', 'MM/DD/YYYY') THEN event END mar,
rn
8 FROM
9 (
10 SELECT groupnm, MONTH, event,
11 row_number( ) over (PARTITION BY groupnm, MONTH ORDER BY Event) rn
12 FROM T
13 )
14 )
15 GROUP BY groupnm, rn ;
GRO RN JAN FEB MAR
--- ---------- --- --- ---
A 1 X XX XXX
A 2 Y YY YYY
A 3 abc
B 1 Z ZZ ZZZ
B 2 xxx
C 1 A AA AAA
6 rows selected.
Here I tested it again with your origianl query
that also shows the result are identical.
SQL> SELECT groupnm,
2 rn,
3 MAX(ev1) "Jan", MAX(ev2) "Feb", MAX(ev3) "Mar"
4 FROM (SELECT
5 groupnm, rn,
6 DECODE( MONTH, TO_DATE('1/1/2002', 'MM/DD/YYYY'), rn, NULL ) rn1,
7 DECODE( MONTH, TO_DATE('1/1/2002', 'MM/DD/YYYY'), event, NULL ) ev1,
8 DECODE( MONTH, TO_DATE('2/1/2002', 'MM/DD/YYYY'), rn, NULL ) rn2,
9 DECODE( MONTH, TO_DATE('2/1/2002', 'MM/DD/YYYY'), event, NULL ) ev2,
10 DECODE( MONTH, TO_DATE('3/1/2002', 'MM/DD/YYYY'), rn, NULL ) rn3,
11 DECODE( MONTH, TO_DATE('3/1/2002', 'MM/DD/YYYY'), event, NULL ) ev3
12 FROM (
13 SELECT groupnm, MONTH, event,
14 row_number() over ( PARTITION BY MONTH, groupnm ORDER BY event) rn
15 FROM T
16 ) T
17 ) T
18 GROUP BY groupnm, rn
19 ;
GRO RN Jan Feb Mar
--- ---------- --- --- ---
A 1 X XX XXX
A 2 Y YY YYY
A 3 abc
B 1 Z ZZ ZZZ
B 2 xxx
C 1 A AA AAA
6 rows selected.
SQL> spool off
SQL solution for "Zain"
Frank Zhou, March 15, 2006 - 3:38 pm UTC
Here is the SQL solution for Zain's question posted on February 03, 2006
Thanks,
Frank
This SQL is the equivlent of the "pivot" table
SELECT LEVEL le FROM dual
CONNECT BY LEVEL < ADD_MONTHS(TRUNC(SYSDATE, 'y'), 12) - TRUNC(SYSDATE, 'y')
SQL> SELECT (TO_DATE('01-Jan-2006','dd-mon-yyyy') + p.x) DA,
2 MAX(CASE WHEN t.name = 'JOE' THEN t.job_no END) AS JOE,
3 MAX(CASE WHEN t.name = 'SAM' THEN t.job_no END) AS SAM,
4 MAX(CASE WHEN t.name = 'JOHN' THEN t.job_no END) AS JOHN
5 FROM proj_time t,
6 (
7 SELECT le x
8 FROM
9 ( SELECT LEVEL le FROM dual
10 CONNECT BY LEVEL <
ADD_MONTHS(TRUNC(SYSDATE, 'y'), 12) - TRUNC(SYSDATE, 'y')
11 )
12 ) p
13 WHERE (TO_DATE('01-Jan-2006','dd-mon-yyyy') + p.x) BETWEEN
14 TO_DATE('01-Jan-2006','dd-mon-yyyy')
AND TO_DATE('28-Feb-2006','dd-mon-yyyy')
15 AND (TO_DATE('01-Jan-2006','dd-mon-yyyy') + p.x) = t.booked (+)
16 GROUP BY x
17 ORDER BY 1, 2, 3;
DA JOE SAM JOHN
--------- ---------- ---------- ----------
02-JAN-06
03-JAN-06
04-JAN-06
05-JAN-06
06-JAN-06
07-JAN-06
08-JAN-06
09-JAN-06
10-JAN-06 JOB001
11-JAN-06 JOB001 JOB001
12-JAN-06 JOB001 JOB001
DA JOE SAM JOHN
--------- ---------- ---------- ----------
13-JAN-06 JOB001 JOB001
14-JAN-06 JOB001 JOB002
15-JAN-06 JOB001 JOB002
16-JAN-06 JOB002
17-JAN-06
18-JAN-06
19-JAN-06
20-JAN-06
21-JAN-06
22-JAN-06
23-JAN-06
DA JOE SAM JOHN
--------- ---------- ---------- ----------
24-JAN-06
25-JAN-06
26-JAN-06
27-JAN-06
28-JAN-06
29-JAN-06
30-JAN-06
31-JAN-06
01-FEB-06 JOB003
02-FEB-06 JOB003
03-FEB-06 JOB003 JOB004
DA JOE SAM JOHN
--------- ---------- ---------- ----------
04-FEB-06 JOB003 JOB004
05-FEB-06 JOB003 JOB004
06-FEB-06 JOB003 JOB004
07-FEB-06 JOB004
08-FEB-06
09-FEB-06
10-FEB-06
11-FEB-06
12-FEB-06
13-FEB-06
14-FEB-06
DA JOE SAM JOHN
--------- ---------- ---------- ----------
15-FEB-06
16-FEB-06
17-FEB-06
18-FEB-06
19-FEB-06
20-FEB-06
21-FEB-06
22-FEB-06
23-FEB-06
24-FEB-06
25-FEB-06
DA JOE SAM JOHN
--------- ---------- ---------- ----------
26-FEB-06
27-FEB-06
28-FEB-06
58 rows selected.
Or You can do it .
SQL> SELECT p.x,
2 MAX(CASE WHEN t.name = 'JOE' THEN t.job_no END) AS JOE,
3 MAX(CASE WHEN t.name = 'SAM' THEN t.job_no END) AS SAM,
4 MAX(CASE WHEN t.name = 'JOHN' THEN t.job_no END) AS JOHN
5 FROM proj_time t,
6 (
7 SELECT TRUNC(SYSDATE, 'y') -1 + le x
8 FROM
9 (
10 SELECT LEVEL le FROM dual
11 CONNECT BY LEVEL <
ADD_MONTHS(TRUNC(SYSDATE, 'y'), 12) - TRUNC(SYSDATE, 'y')
12 )
13 ) p
14 WHERE p.x BETWEEN
15 TO_DATE('01-Jan-2006','dd-mon-yyyy')
AND TO_DATE('28-Feb-2006','dd-mon-yyyy')
16 AND p.x = t.booked (+)
17 GROUP BY x
18 ORDER BY 1, 2, 3;
59 rows selected.
SQL> spool off
Zain, March 27, 2006 - 12:09 am UTC
Thanks Frank for an answer to this question. I had been cracking my head over it for sometime.
Did not find something generic in this post
rcm, February 15, 2009 - 12:36 am UTC
Hi,
The page has been awesome having a lot of options for doing crosstab queries. But it would be really useful, if you can help people with querying without fixed field like Jan / Feb. if I have 100 which I can only query from another table, I could not get information in this post. Please try to include such things if possible. Thanks!
February 16, 2009 - 12:25 pm UTC
well, in order to pivot rows into columns....
you actually DO need to know the data, else it is quite impossible - think about it for a minute.
The resulting query will always have a fixed number of columns - always (SQL says so)
The data that goes into column 1 - what is it, what should it be? only you - ONLY YOU - know the answer to that.
pivot
A reader, March 06, 2009 - 8:44 pm UTC
Hi Tom,
We have a table with cal_year, and columns jan, feb....dec. The Financial Year is from Jul-Jun.
We want to get the result set in rows for last three months with every batch process in the output table.
Input Qty:
cal_year Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2008 1 2 3 4 5 6 7 8 9 10 11 12
-- ..fy 2008 >> < fy 2009....
2009 10 20 30 40 50 60 70 80 90 100 110 120
-- ..>> < fY 2010....
Output required for last three months
Processing month AUG 2008
Fin_year_month Qty
200908 8
200907 7
200806 6
Can this be done in one query?
Thanks
March 07, 2009 - 12:28 pm UTC
who knows? we don't see any table creates or inserts to play with, we have NOT ANY CLUE AT ALL what "with every batch process in the output table" is or means
I think I know what you mean - but it'll be ugly (because the data is stored like a non-database person using a spreadsheet would store - eg: wrong), but one would like the setup and the explanation before taking time to type in the ugly query.
and make sure to make the example more interesting, tell us what happens when Jan or Dec is the input month - August - borrrrinnnng. (or tell us, Jan and Dec cannot be queried against)
Pivot
A reader, March 08, 2009 - 4:59 am UTC
Hi Tom,
No Control on source...
(Every row is identified by the cal year it belongs)
In two steps shouldn't this be easy(but NOT efficient)
1) Load data in new table (cal_year, month, qty, fy-> null) by converting columns to rows for last two years based upon cal_year
2) For each row, update column FY (YYYYMM) based upon the logic for definition of FY through PL/SQL
I was thinking that may be some analytical function/approach :-)
Thanks
March 08, 2009 - 7:29 am UTC
you didn't address anything I asked you to.
we can do this in sql, in a single statement, only you really need to fill in the blanks and provide the set up for the example (creates, inserts - like you see in all of my examples)
this will NEVER involve "loading data into another table"
To say that would not be efficient would be the understatment of the century.
The input to your query will be year and month (august, 2008 - for example)
we just need to get the three rows 2007, 2008, 2009 - pivot them, and get the three rows we are interested in
ops$tkyte%ORA10GR2> create table t
2 ( yr number, jan number(3), feb number(3), mar number(3),
3 apr number(3), may number(3), jun number(3), jul number(3),
4 aug number(3), sep number(3), oct number(3), nov number(3),
5 dec number(3));
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec gen_data( 'T', 5 );
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> update t set yr = 2005+rownum;
5 rows updated.
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from t;
YR JAN FEB MAR APR MAY JUN
---------- ---------- ---------- ---------- ---------- ---------- ----------
JUL AUG SEP OCT NOV DEC
---------- ---------- ---------- ---------- ---------- ----------
2006 256 416 611 185 563 980
515 556 231 175 649 521
2007 281 891 956 250 44 514
431 947 775 21 678 719
2008 695 109 450 884 526 287
447 758 492 848 687 77
2009 325 610 566 629 621 933
283 973 789 342 139 315
2010 465 605 285 974 942 307
292 14 866 42 842 677
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> variable y number
ops$tkyte%ORA10GR2> variable m varchar2(3)
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec :m := 'Aug'; :y := 2008
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> with data
2 as
3 (select level l from dual connect by level <= 12)
4 select *
5 from (
6 select yr, case when l <= 6 then yr else yr+1 end fy,
7 to_date( to_char(yr,'fm0000')||'-'||to_char(l,'fm00'), 'yyyy-mm' ) the_dt,
8 decode( l, 1, jan, 2, feb, 3, mar, 4, apr, 5, may, 6, jun,
9 7, jul, 8, aug, 9, sep,10, oct,11, nov,12, dec) val
10 from t, data
11 where yr between :y-1 and :y+1
12 )
13 where the_dt between
14 add_months( to_date( to_number(:y,'fm0000')||'-'||:m, 'yyyy-mon' ),-2) and
15 to_date( to_char(:y,'fm0000')||'-'||:m, 'yyyy-mon' )
16 order by the_dt
17 /
YR FY THE_DT VAL
---------- ---------- --------- ----------
2008 2008 01-JUN-08 707
2008 2009 01-JUL-08 997
2008 2009 01-AUG-08 855
ops$tkyte%ORA10GR2> exec :m := 'Dec'; :y := 2008
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> /
YR FY THE_DT VAL
---------- ---------- --------- ----------
2008 2009 01-OCT-08 750
2008 2009 01-NOV-08 257
2008 2009 01-DEC-08 650
ops$tkyte%ORA10GR2> exec :m := 'Jan'; :y := 2008
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> /
YR FY THE_DT VAL
---------- ---------- --------- ----------
2007 2008 01-NOV-07 66
2007 2008 01-DEC-07 783
2008 2008 01-JAN-08 554
ops$tkyte%ORA10GR2>
Thanks a lot!
A reader, March 09, 2009 - 6:05 am UTC