Oracle 'Partition By' and 'Row_Number' keyword along with pivot

Question

I have this query written by someone else and I am trying to figure out how is it working. I have general idea about all these things such as row_number() , partition by, pivot but I am unable to understand them all together.

For this query :

select
    d, p, s, a  
from
(
    select name,occupation, (ROW_NUMBER() OVER (partition by occupation order by name)) as rownumber from occupations 
)
pivot
(
    max(name) 
    for occupation 
    in ('Doctor' as d, 'Professor' as p, 'Singer' as s, 'Actor' as a) 
) 
order by rownumber;

This is the input table on which the above query works :

This it the output generated by the query which is correct as per the question :

Jenny    Ashley     Meera  Jane
Samantha Christeen  Priya  Julia
NULL     Ketty      NULL   Maria

Now, I want to know how the output is generated by the query i.e. step by step with flow of execution. Explanation with easy examples matching the above situation would be much appreciated. Thanks in advance.

Whosoever downvoted, please mention the reason – Shivam Arora Jul 06 '17 at 13:41 — Shivam Arora, Jul 06 '17 at 13:41

score -1 · Answer 1 · edited Jul 06 '17 at 18:03

-1

After from clause you have following :

select name,occupation, (ROW_NUMBER() OVER (partition by occupation order by name))

Above virtually restack your table data in three columns - Name, occupation, rownumber. rownumber will reset itself as soon as occupation column changes. Output data will be like :

NAME                 OCCUPATION            ROWNUMBER
-------------------- -------------------- ----------
Jane                 ACTOR                1
Julia                ACTOR                2
Maria                ACTOR                3
JENNY                DOCTOR               1 <-- rownumber reset to 1
Sammantha            DOCTOR               2

Pivot function let you aggregate result & rotate rows into columns. Pivot usage code is :

PIVOT 
(
  aggregate_function(column2)
  FOR column2
  IN ( expr1, expr2, ... expr_n) | subquery
)

So your PIVOT function have name stacked NAME based on OCCUPATION . Each stack (column in output) is ordered by rownumber column inserted via first subquery.

edited Jul 06 '17 at 18:03

Shivam Arora

476
5
18

answered Jul 06 '17 at 16:02

Abhishek Dixit

39
4

Thanks for your time but i myself know this much, what I want is how each time pivot is working with partitioned rows. How is the sql flow. What is executed first followed by what ? It would be great if you can explain me how the answer is coming in detailed steps. – Shivam Arora Jul 06 '17 at 16:37
@ShivamArora - the sequence of execution is exactly as Abhishek described it. You have a subquery, so that is executed first. The result is the "output" as described in this Answer: three columns (name, occupation, rownumber). This output becomes the input to the PIVOT operation - and HOW those three columns were created in the subquery becomes irrelevant (and the PIVOT operation doesn't know, has no way of knowing, and does not NEED to know that the third column was created with the ROW_NUMBER function). – Jul 06 '17 at 17:18
@mathguy : when I remove the `(ROW_NUMBER() OVER (partition by occupation order by name)) as rownumber` from the query, what would be the answer according to you ? – Shivam Arora Jul 07 '17 at 05:14
@ShivamArora - Why are you asking me? Don't you have the query already? You can try it yourself! In any case - if you remove the row_number from the subquery, then the grouping performed by PIVOT will result in one row per profession - so the result will have one row only, for each profession you will get just one name, specifically the last one (MAX) in alphabetical order. Did I pass your quiz? – Jul 07 '17 at 11:39
@mathguy My intention was not to ask any quiz or challenge your expertise. I just want help, if you can then thank you so much if you cannot then thanks as well. sorry If I offended you. – Shivam Arora Jul 07 '17 at 11:42
@ShivamArora - No worries, but seriously: why ask and not try yourself? – Jul 07 '17 at 11:44
@mathguy : I tried it mathguy but I am not able to understand how is it happening. – Shivam Arora Jul 07 '17 at 11:53
@ShivamArora - If you remove part of query as you want, your query will return only one row, which will be top row of output you have. If you due to rownum column, Pivot clause is forced to select all max values of name for given rownum values. Hope this clarifies bit more. - Abhi – Abhishek Dixit Jul 07 '17 at 16:05

Oracle 'Partition By' and 'Row_Number' keyword along with pivot

1 Answers1