Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: August 23, 2016 - 6:46 am UTC

Last updated: August 23, 2016 - 1:25 pm UTC

Version: 10g

Viewed 1000+ times

You Asked

Hello,

I have a table something like this:

book name| chapter| sub-chapter | no. of pages|

book1 chap1 sub_book1_a1 230
book1 chap1 sub_book1_a2 110
book1 chap1 sub_book1_a3 220
book1 chap2 sub_book1_b1 760
book1 chap2 sub_book1_b2 530
book2 chap1 sub_book2_a1 652
book2 chap1 sub_book2_a2 457
book2 chap2 sub_book2_b1 673

.
.
.

I want to pivot the table to be as following:

book book1 book1 .......
chapter chap1 chap1
sub-chapter sub_book1_a1 sub_book1_a2
no. of pages 230 110

the issue is that I can't know exactly how many entries i will have it the original table, I do know that each entry will appear only once (it's an aggregated table). so I need to build it in a dynamic way.
the most critical restriction that I have is that the user can use only views or functions.

I read the answers reagarding this issue...
I understad is that the best practice for that is to:
1. calculate the number of rows in the original table.
2. build a dynamic sql using a loop.

can anyone give me a simple example on how to do so?

thanks,
Mike



and Chris said...

First up, it's helpful to figure out what the final you want is.

To convert the numbers of pages to columns, list out all the subchapters you want to convert in your pivot clause:

CREATE TABLE t (
  book_name varchar2(5), chapter varchar2(5), 
  subchapter varchar2(12), no_of_pages int
);
    
INSERT INTO t VALUES ('book1', 'chap1', 'sub_book1_a1', 230);
insert into t values ('book1', 'chap1', 'sub_book1_a2', 110);
insert into t values ('book1', 'chap1', 'sub_book1_a3', 220);
insert into t values ('book1', 'chap2', 'sub_book1_b1', 760);
insert into t values ('book1', 'chap2', 'sub_book1_b2', 530);
insert into t values ('book2', 'chap1', 'sub_book2_a1', 652);
insert into t values ('book2', 'chap1', 'sub_book2_a2', 457);
insert into t values ('book2', 'chap2', 'sub_book2_b1', 673);

select * from (
  select subchapter, no_of_pages from t
)
pivot (
  min(no_of_pages) for subchapter in (
    'sub_book1_a1' b1a1, 'sub_book1_a2' b1a2, 'sub_book1_a3' b1a3,
    'sub_book1_b1' b1b1, 'sub_book1_b2' b1b2, 'sub_book2_a1' b2a1, 
    'sub_book2_a2' b2a2, 'sub_book2_b1' b2b1
  )
);

B1A1  B1A2  B1A3  B1B1  B1B2  B2A1  B2A2  B2B1  
230   110   220   760   530   652   457   673


So you need to generate a list of all the subchapters. You can do this with plain SQL using the following listagg() statement :

select listagg('''' || subchapter || ''' as ' || subchapter, ',' || chr(10)) 
         within group (order by subchapter) cols
from   t;

COLS                                                                                                                                                                                                                                                            
'sub_book1_a1' as sub_book1_a1,
'sub_book1_a2' as sub_book1_a2,
'sub_book1_a3' as sub_book1_a3,
'sub_book1_b1' as sub_book1_b1,
'sub_book1_b2' as sub_book1_b2,
'sub_book2_a1' as sub_book2_a1,
'sub_book2_a2' as sub_book2_a2,
'sub_book2_b1' as sub_book2_b1


So to create your pivot you just need to build a dynamic string based on this. 

declare
  stmt        varchar2(4000);
  pivot_clause varchar2(1000);
begin

  select listagg('''' || subchapter || ''' as ' || subchapter, ',') within group (order by subchapter) 
  into   pivot_clause
  from   t
  where  book_name = 'book1';  

  stmt := 'select * from (
  select book_name, subchapter, no_of_pages from t
)
pivot (
  min(no_of_pages) for subchapter in (' || pivot_clause || ')
)';
  
  dbms_output.put_line(stmt);
  
  execute immediate stmt;
end;
/

select * from (
  select book_name, subchapter, no_of_pages from t
)
pivot (
  min(no_of_pages) for subchapter in ('sub_book1_a1' as sub_book1_a1,'sub_book1_a2' as sub_book1_a2,'sub_book1_a3' as sub_book1_a3,'sub_book1_b1' as sub_book1_b1,'sub_book1_b2' as sub_book1_b2)
)


There are complicating factors in your case though.

You've said you're on 10g. Pivot and listagg only came in 11g!

So instead of listagg, you could use stragg:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:2196162600402

The alternative to pivot is harder. Here you need to dynamically build you select clause. Each column will be something like:

min ( case when subchapter = 'chapter name' then subchapter end ) as 'chapter name'


This would give a listagg statement like:

select listagg('min ( case when subchapter = ''' || subchapter || ''' then no_of_pages end ) as ' || subchapter, ',' || chr(10)) 
         within group (order by subchapter) cols
from   t;

COLS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
min ( case when subchapter = 'sub_book1_a1' then no_of_pages end ) as sub_book1_a1,
min ( case when subchapter = 'sub_book1_a2' then no_of_pages end ) as sub_book1_a2,
min ( case when subchapter = 'sub_book1_a3' then no_of_pages end ) as sub_book1_a3,
min ( case when subchapter = 'sub_book1_b1' then no_of_pages end ) as sub_book1_b1,
min ( case when subchapter = 'sub_book1_b2' then no_of_pages end ) as sub_book1_b2,
min ( case when subchapter = 'sub_book2_a1' then no_of_pages end ) as sub_book2_a1,
min ( case when subchapter = 'sub_book2_a2' then no_of_pages end ) as sub_book2_a2,
min ( case when subchapter = 'sub_book2_b1' then no_of_pages end ) as sub_book2_b1


Use this to dynamically build a query like:

select min ( case when subchapter = 'sub_book1_a1' then no_of_pages end ) as sub_book1_a1,
min ( case when subchapter = 'sub_book1_a2' then no_of_pages end ) as sub_book1_a2,
min ( case when subchapter = 'sub_book1_a3' then no_of_pages end ) as sub_book1_a3,
min ( case when subchapter = 'sub_book1_b1' then no_of_pages end ) as sub_book1_b1,
min ( case when subchapter = 'sub_book1_b2' then no_of_pages end ) as sub_book1_b2,
min ( case when subchapter = 'sub_book2_a1' then no_of_pages end ) as sub_book2_a1,
min ( case when subchapter = 'sub_book2_a2' then no_of_pages end ) as sub_book2_a2,
min ( case when subchapter = 'sub_book2_b1' then no_of_pages end ) as sub_book2_b1
from t;

SUB_BOOK1_A1  SUB_BOOK1_A2  SUB_BOOK1_A3  SUB_BOOK1_B1  SUB_BOOK1_B2  SUB_BOOK2_A1  SUB_BOOK2_A2  SUB_BOOK2_B1  
230           110           220           760           530           652           457           673


Secondly, it looks like you want to do more than a simple pivot. It seems you want to transpose the rows and columns aka a pivot + unpivot.

This is going to make your dynamic SQL harder!

For static example of how to do this, see:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9531337800346950622

Rating

  (2 ratings)

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

Comments

UNPIVOT then PIVOT

Stew Ashton, August 23, 2016 - 10:54 am UTC

This is a lot easier if you unpivot first. The UNPIVOT operation is not dynamic to start with. When you PIVOT, you can use artificial column names so you just need to generate the right number.

Static 11g solution:
with string_data as (
  select book,chapter,subchapter, pages||null pages,
  row_number() over(order by book,chapter,subchapter) rn
  from t
)
select * from string_data
unpivot(val for col in (book, chapter, subchapter, pages))
pivot(max(val) for rn in (1,2,3,4,5,6,7,8,9))
order by case col
  when 'BOOK' then 1 when 'CHAPTER' then 2 when 'SUBCHAPTER' then 3 else 4 end;

The only dynamic part is the IN() list.


Static 10g solution:
with string_data as (
  select t.*,
  row_number() over(order by book,chapter,subchapter) rn
  from t
)
, cols as (
  select 1 orderby, 'BOOK' col from dual
  union all
  select 2, 'CHAPTER' from dual
  union all
  select 3, 'SUBCHAPTER' from dual
  union all
  select 4, 'PAGES' from dual
)
, unpivoted as (
  select rn, orderby, col,
    case col when 'BOOK' then book
      when 'CHAPTER' then chapter
      when 'SUBCHAPTER' then subchapter
      else pages||null
    end val
  from string_data, cols
)
select col,
  max(case rn when 1 then val end) "1",
  max(case rn when 2 then val end) "2",
  max(case rn when 3 then val end) "3",
  max(case rn when 4 then val end) "4",
  max(case rn when 5 then val end) "5",
  max(case rn when 6 then val end) "6",
  max(case rn when 7 then val end) "7",
  max(case rn when 8 then val end) "8"
from unpivoted
group by orderby, col
order by orderby;
Here the dynamic part is the number of lines starting with 'max('.
Chris Saxon
August 23, 2016 - 1:25 pm UTC

Nice work, thanks Stew.

Unpeeling the onion

Duke Ganote, August 23, 2016 - 6:43 pm UTC

I'd've never guessed you could unpivot and pivot in a single query. I had to pull the layers apart to figure out what was happening. 

column 1 format a5
column 2 format a5
column 3 format a5
column 4 format a5
column 5 format a5
column 6 format a5
column 7 format a5
column 8 format a5
column 9 format a5
WITH
o ( COLumn_nm   , col# ) AS (                         SELECT
    'BOOK'      , 1               FROM DUAL UNION ALL SELECT
    'CHAPTER'   , 2               FROM DUAL UNION ALL SELECT
    'SUBCHAPTER', 3               FROM DUAL UNION ALL SELECT
    'PAGES'     , 4               FROM DUAL
),
t (  book, chapter, subchapter, pages ) AS (             SELECT
    'book1', 'chap1', '1.1'  , 230    FROM DUAL UNION ALL SELECT
    'book1', 'chap1', '1.2'  , 110    FROM DUAL UNION ALL SELECT
    'book1', 'chap1', '1.3'  , 220    FROM DUAL UNION ALL SELECT
    'book1', 'chap2', '1.1'  , 760    FROM DUAL UNION ALL SELECT
    'book1', 'chap2', '1.2'  , 530    FROM DUAL UNION ALL SELECT
    'book2', 'chap1', '2.1'  , 652    FROM DUAL UNION ALL SELECT
    'book2', 'chap1', '2.2.1', 457    FROM DUAL UNION ALL SELECT
    'book2', 'chap2', '2.2.2', 673    FROM DUAL
)
,
string_data as (
  select book,chapter,subchapter, pages||'' pages,
  row_number() over(order by book,chapter,subchapter) r#
  from t
)
, up AS (
select * 
  from string_data 
unpivot(col_val for col_nm in 
          (book, chapter, subchapter, pages)) -- hardcoded
)
, p AS (
select * 
  from up
pivot(max(col_val) for r# in (1,2,3,4,5,6,7,8,9)) -- hardcoded
)
SELECT x.*
  FROM p x
  JOIN o
    ON x.col_nm = o.column_nm
 ORDER BY col#;

COL_NM     1     2     3     4     5     6     7     8     9
---------- ----- ----- ----- ----- ----- ----- ----- ----- -----
BOOK       book1 book1 book1 book1 book1 book2 book2 book2
CHAPTER    chap1 chap1 chap1 chap2 chap2 chap1 chap1 chap2
SUBCHAPTER 1.1   1.2   1.3   1.1   1.2   2.1   2.2.1 2.2.2
PAGES      230   110   220   760   530   652   457   673

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 channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

DBMS_OUTPUT

More on PL/SQL routine DBMS_OUTPUT here