Skip to Main Content

Breadcrumb

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...
more

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