Thanks for the question, Mark.

Asked: December 16, 2002 - 9:20 am UTC

Last updated: March 08, 2009 - 7:29 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

I'm trying to do a complicated 2-way pivot in SQL, and can't work
out where to start. Assume a table as follows

Event Group Month
----- ----- ----
X A Jan
Y A Jan
Z B Jan
A C Jan
XX A Feb
YY A Feb
ZZ B Feb
AA C Feb
XXX A Mar
YYY A Mar
ZZZ B Mar
AAA C Mar

Note that each event/group combo is unique, the list of groups is
reasonably fixed, but there can be many months. each group can have 1 or more events, varied by month. Basically this is a schedule application - the groups represent internal development groups, the
events are milestones within the month.

I need something like

Jan Feb Mar
---------------------------
A X XX XXX
A Y YY YYY
B Z ZZ ZZZ
C A AA AAA

The only way I can see to do this is with some rather ugly UNION
statements. Any Ideas?

and Tom said...

Of course, you must first query out the:

ops$tkyte@ORA920> select min(month), max(month), months_between(max(month),min(month))+1 num_months
2 from t;

MIN(MONTH) MAX(MONTH) NUM_MONTHS
----------- ----------- ----------
01-jan-2002 01-mar-2002 3

And we'll be building this query dynamically (have to -- you said the number of months varies greatly -- SQL wants to know the number of columns hard and fast - the number of columns in this set of data is 4 -- groupname and 3 months of data...)

Then given this data (added some spurious rows abc, xxx just to make sure there were uneven groups in there)

ops$tkyte@ORA920> select groupnm, month, event
2 from t
3 order by groupnm, month, event;

G MONTH EVE
- ----------- ---
A 01-jan-2002 X
A 01-jan-2002 Y
A 01-jan-2002 abc
A 01-feb-2002 XX
A 01-feb-2002 YY
A 01-mar-2002 XXX
A 01-mar-2002 YYY
B 01-jan-2002 Z
B 01-feb-2002 ZZ
B 01-mar-2002 ZZZ
B 01-mar-2002 xxx
C 01-jan-2002 A
C 01-feb-2002 AA
C 01-mar-2002 AAA

14 rows selected.

We can do the following (I'll build the answer iteratively -- the answer is at the end...)

We'll first start by assigning a row_number() to each event in a group by month:

ops$tkyte@ORA920> select event, month, groupnm,
2 row_number() over ( partition by month, groupnm
3 order by event ) rn
4 from t
5 /

EVE MONTH G RN
--- ----------- - ----------
X 01-jan-2002 A 1
Y 01-jan-2002 A 2
abc 01-jan-2002 A 3
Z 01-jan-2002 B 1
A 01-jan-2002 C 1
XX 01-feb-2002 A 1
YY 01-feb-2002 A 2
ZZ 01-feb-2002 B 1
AA 01-feb-2002 C 1
XXX 01-mar-2002 A 1
YYY 01-mar-2002 A 2
ZZZ 01-mar-2002 B 1
xxx 01-mar-2002 B 2
AAA 01-mar-2002 C 1

14 rows selected.

Then, well begin the PIVOT process using decode:

ops$tkyte@ORA920> select groupnm,
2 decode( month, '01-jan-2002', rn, null ) rn1,
3 decode( month, '01-jan-2002', event, null ) ev1,
4 decode( month, '01-feb-2002', rn, null ) rn2,
5 decode( month, '01-feb-2002', event, null ) ev2,
6 decode( month, '01-mar-2002', rn, null ) rn3,
7 decode( month, '01-mar-2002', event, null ) ev3
8 from ( select event, month, groupnm,
9 row_number() over ( partition by month, groupnm
10 order by event ) rn
11 from t
12 ) t
13 /

G RN1 EV1 RN2 EV2 RN3 EV3
- ---------- --- ---------- --- ---------- ---
A 1 X
A 2 Y
A 3 abc
B 1 Z
C 1 A
A 1 XX
A 2 YY
B 1 ZZ
C 1 AA
A 1 XXX
A 2 YYY
B 1 ZZZ
B 2 xxx
C 1 AAA

14 rows selected.

Now, we need to squash out those NULL entries, slide things up. We'll use a 9i new function coalese in this example, but we could use

nvl( rn1, nv(rn2, rn3) )

instead in 8i. Coalesce returns the first NON-NULL entry from a list of values. We'll group by GROUPNM and the coalesce of all of those rn's:

ops$tkyte@ORA920> select groupnm,
2 coalesce( rn1, 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, coalesce( rn1, 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.

ops$tkyte@ORA920>

throw in a little SQLPlus formatting and there you go:

ops$tkyte@ORA920> break on groupnm skip 1
ops$tkyte@ORA920> /

G RN Jan Feb Mar
- ---------- --- --- ---
A 1 X XX XXX
2 Y YY YYY
3 abc

B 1 Z ZZ ZZZ
2 xxx

C 1 A AA AAA

6 rows selected.

A nice matrix that shows by GROUP what events are taking place in each month...

QED -- analytics rock

Rating

(35 ratings)

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

Comments

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

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Brilliant

A Date Column to pivit into Week1, Week2.....

Pls Repeat it....

using Oracle 8i

pivot question

Pivot

about last post: Pivot

Pivots are great!

coalesce -- but rows

coalesche -- but rows

sql soln for pivot Db ver 9.2

When overlap

the analysis

PIVOT

Sorry typo 65 should be 64

4th Row because of explode

Pls see the required corrected output.

Lets put 4th row aside

IT should be select * from t and not select * from vt

Pivoting

Amazing

Expert One on One Oracle

The "coalesce" function in your answer for the original posted question is not required.

SQL solution for "Zain"

Did not find something generic in this post

pivot

Pivot

Thanks a lot!

More to Explore

Analytics

Truly complex pivot

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Brilliant

A Date Column to pivit into Week1, Week2.....

Pls Repeat it....

using Oracle 8i

pivot question

Pivot

about last post: Pivot

Pivots are great!

coalesce -- but rows

coalesche -- but rows

sql soln for pivot Db ver 9.2

When overlap

the analysis

PIVOT

Sorry typo 65 should be 64

4th Row because of explode

Pls see the required corrected output.

Lets put 4th row aside

IT should be select * from t and not select * from vt

Pivoting

Amazing

Expert One on One Oracle

The "coalesce" function in your answer for the original posted question is not required.

SQL solution for "Zain"

Did not find something generic in this post

pivot

Pivot

Thanks a lot!

More to Explore

Analytics