Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, potential.

Asked: December 19, 2007 - 1:36 pm UTC

Last updated: December 21, 2007 - 2:55 pm UTC

Version: 9.2.0

Viewed 1000+ times

You Asked

Tom,

I have the following two tables:

table product (it grows when there is a new product being defined)
product_type description
p1 d1
p2 d2
... ...
p100 d100

table p_pt
person product_type
johndoe p1
johndoe p1
johndoe p1
johndoe p2
johndoe p3
janedoe p1
janedoe p2
janedoe p2
janedoe p3
janedoe p3
...

I need to run a report which outputs in the following format:
person d1 d2 d3
johndoe 3 1 1
janedoe 1 2 2

if table p_pt has one more row:
janedoe p4
then the report output will be:
person d1 d2 d3 d4
johndoe 3 1 1 0
janedoe 1 2 2 1

It is the aggregation-then-pivot, the difficult part is the determination of the dynamic headings of those columns, i.e., d1, d2, d3, d4. I don't want show all the possible d1 to d100 when they are sparsely populated. Please give me some suggestion how I can achieve the above.

Thanks,

and Tom said...

You would have to

a) run a query to figure out what columns you wanted
b) use the output of a) to dynamically construct the query you desire.


A sql statement must

a) have a fixed number of columns
b) with static column names

at parse (prepare) time. Given your requirement, you would have to dynamically generate such a query.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:124812348063#41097616566309

Rating

  (4 ratings)

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

Comments

SQL solution

Michel Cadot, December 20, 2007 - 4:22 am UTC


Follow two solutions in SQL depending if you know the maximum number of product types or not.
SQL> select * from p_pt order by 1, 2;

PERSON     PRODUCT_TY
---------- ----------
janedoe    p1
janedoe    p2
janedoe    p2
janedoe    p3
janedoe    p3
johndoe    p1
johndoe    p1
johndoe    p1
johndoe    p2
johndoe    p3

SQL> -- Maximum number of columns known, say 5
SQL> set head off
SQL> set feed off
SQL> col nop noprint
SQL> col person format a10
SQL> col val format a3
SQL> with 
  2    product_types as (
  3      select distinct 
  4             product_type,
  5             dense_rank () over (order by product_type) rk
  6      from p_pt
  7    )
  8  select 1 nop, 'person' person, 
  9         lpad(max(decode(rk,1,product_type)),3) val,
 10         lpad(max(decode(rk,2,product_type)),3) val,
 11         lpad(max(decode(rk,3,product_type)),3) val,
 12         lpad(max(decode(rk,4,product_type)),3) val,
 13         lpad(max(decode(rk,5,product_type)),3) val
 14  from product_types
 15  union all
 16  select 2 nop, person, 
 17         to_char(sum(decode(a.product_type||'/1',b.product_type||'/'||b.rk,1)),'99') val,
 18         to_char(sum(decode(a.product_type||'/2',b.product_type||'/'||b.rk,1)),'99') val,
 19         to_char(sum(decode(a.product_type||'/3',b.product_type||'/'||b.rk,1)),'99') val,
 20         to_char(sum(decode(a.product_type||'/4',b.product_type||'/'||b.rk,1)),'99') val,
 21         to_char(sum(decode(a.product_type||'/5',b.product_type||'/'||b.rk,1)),'99') val
 22  from p_pt a, product_types b
 23  group by person
 24  order by nop, person
 25  /

person      p1  p2  p3
janedoe      1   2   2
johndoe      3   1   1

SQL> insert into p_pt values ('janedoe', 'p4');
SQL> with 
  2    product_types as (
  3      select distinct 
  4             product_type,
  5             dense_rank () over (order by product_type) rk
  6      from p_pt
  7    )
  8  select 1 nop, 'person' person, 
  9         lpad(max(decode(rk,1,product_type)),3) val,
 10         lpad(max(decode(rk,2,product_type)),3) val,
 11         lpad(max(decode(rk,3,product_type)),3) val,
 12         lpad(max(decode(rk,4,product_type)),3) val,
 13         lpad(max(decode(rk,5,product_type)),3) val
 14  from product_types
 15  union all
 16  select 2 nop, person, 
 17         to_char(sum(decode(a.product_type||'/1',b.product_type||'/'||b.rk,1)),'99') val,
 18         to_char(sum(decode(a.product_type||'/2',b.product_type||'/'||b.rk,1)),'99') val,
 19         to_char(sum(decode(a.product_type||'/3',b.product_type||'/'||b.rk,1)),'99') val,
 20         to_char(sum(decode(a.product_type||'/4',b.product_type||'/'||b.rk,1)),'99') val,
 21         to_char(sum(decode(a.product_type||'/5',b.product_type||'/'||b.rk,1)),'99') val
 22  from p_pt a, product_types b
 23  group by person
 24  order by nop, person
 25  /

person      p1  p2  p3  p4
janedoe      1   2   2   1
johndoe      3   1   1

SQL> rollback;
SQL> -- Maximum number of columns unknown
SQL> col val format a80
SQL> with 
  2    product_types as (
  3      select distinct 
  4             product_type,
  5             dense_rank () over (order by product_type) rk,
  6             count(distinct product_type) over () cnt
  7      from p_pt
  8    ),
  9    persons as ( select distinct person from p_pt )
 10  select 1 nop, 'person' person,
 11         translate(substr(sys_connect_by_path(lpad(product_type,3),'/'),2),'/',' ') val
 12  from product_types
 13  where rk = cnt
 14  connect by prior rk = rk - 1
 15  start with rk = 1
 16  union all
 17  select 2 nop, person,
 18         translate(substr(sys_connect_by_path(to_char(val,'99'),'/'),2),'/',' ') val
 19  from ( select a.person, b.rk, b.cnt,
 20                nvl((select sum(1) from p_pt c 
 21                     where c.person = a.person and c.product_type = b.product_type)
 22                    ,0) val
 23         from persons a, product_types b
 24         group by a.person, b.product_type, b.rk, b.cnt
 25       )
 26  where rk = cnt
 27  connect by prior rk = rk - 1 and prior person = person
 28  start with rk = 1
 29  order by nop, person
 30  /

person      p1  p2  p3
janedoe      1   2   2
johndoe      3   1   1

SQL> insert into p_pt values ('janedoe', 'p4');
SQL> with 
  2    product_types as (
  3      select distinct 
  4             product_type,
  5             dense_rank () over (order by product_type) rk,
  6             count(distinct product_type) over () cnt
  7      from p_pt
  8    ),
  9    persons as ( select distinct person from p_pt )
 10  select 1 nop, 'person' person,
 11         translate(substr(sys_connect_by_path(lpad(product_type,3),'/'),2),'/',' ') val
 12  from product_types
 13  where rk = cnt
 14  connect by prior rk = rk - 1
 15  start with rk = 1
 16  union all
 17  select 2 nop, person,
 18         translate(substr(sys_connect_by_path(to_char(val,'99'),'/'),2),'/',' ') val
 19  from ( select a.person, b.rk, b.cnt,
 20                nvl((select sum(1) from p_pt c 
 21                     where c.person = a.person and c.product_type = b.product_type)
 22                    ,0) val
 23         from persons a, product_types b
 24         group by a.person, b.product_type, b.rk, b.cnt
 25       )
 26  where rk = cnt
 27  connect by prior rk = rk - 1 and prior person = person
 28  start with rk = 1
 29  order by nop, person
 30  /

person      p1  p2  p3  p4
janedoe      1   2   2   1
johndoe      3   1   1   0

In this second query, I gave another way to get the right row using count (rk=cnt) instead of max.

Regards
Michel

Tom Kyte
December 20, 2007 - 10:08 am UTC

but you have not made the column names person, p1, p2, p3, p4

you have made a row of data be that (and therefore coerce all of the values in the result set to be strings).

potential, December 20, 2007 - 9:28 am UTC

Great solutions. Many thanks to Tom and Michel.

SQL solution

Michel Cadot, December 20, 2007 - 11:27 am UTC


You're right Tom but "potential" talked about report.
Are not reports just strings?

Im' a little bit insincere on this one. :)

Regards
Michel
Tom Kyte
December 20, 2007 - 2:34 pm UTC

:)

I think most reporting tools would dump the column headings as part of their processing - they wouldn't "know" to look at the first row.

11g pivot clause?

Kim Berg Hansen, December 21, 2007 - 3:59 am UTC

I know the original question was for version 9.
But I'm curious about the pivot clause in 11g.

As far as I can tell the pivot clause requires that you list the columns and names for the columns in the pivot_for_clause.
So even with 11g, the solution to this question would still require two steps - first find the possible values/columnnames, then build a dynamic sql statement using that list in the pivot_for_clause?
Or is it possible to do it differently in 11g?

A theoretical sub-question: Do you think it technologically possible, that in a future release, the pivot clause would permit a subquery as pivot_for_clause, thus making this possible in one step?

Thanks always for sharing your genius :-)

Regards

Kim Berg Hansen

Tom Kyte
December 21, 2007 - 2:55 pm UTC

SQL needs to know

a) the number of columns being selected
b) their names
c) their types

it cannot vary from execution to execution

I doubt the SQL people (that make the standard) will change those facts...