Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Felipe.

Asked: October 19, 2016 - 7:31 pm UTC

Last updated: October 21, 2016 - 1:39 am UTC

Version: 10.1.2

Viewed 1000+ times

You Asked

Hi friends!

I tried to use pivot, unpivot and other ways to return the expected, but i'm not successfully.

create table t1 (ID NUMBER(5), tp char(1), nm varchar2(5), st number(2), en number(2));

  insert into t1 values (1,'A', 'a', 0, 5);
  insert into t1 values (2,'B', 'b', 6, 9);
  insert into t1 values (3,'C', 'c', 10, 14);
  insert into t1 values (4,'D', 'd', 15, 20);
  insert into t1 values (5,'A', 'a', 0, 5);
  insert into t1 values (6,'B', 'b', 6, 15);
  insert into t1 values (7,'C', 'c', 16, 25);
  insert into t1 values (8,'D', 'd', 26, 35);

select * from t1;
/*
ID  TP  NM  ST  EN
1   A   a   0    5
2   B   b   6    9
3   C   c   10  14
4   D   d   15  20
5   A   a   0    5 
6   B   b   6   15
7   C   c   16  25
8   D   d   26  35

*/


What I need:
TP_A  ST_A  EN_A  TP_B  ST_B  EN_B  TP_C  ST_C  EN_C  TP_D  ST_D  EN_D 
a      0    5     b     6     9     c     10    14    d     15    20
a      0    5     b     6    15     c     16    25    d     25    36


Could you help me please?

Thanks!
Dal Pizzol.

and Connor said...

You need *something* that determines that we are batching things in groups of 4 rows. There's nothing in the table that tells us this.

I've *invented* such a column using rownun, but that's not practical in a real situation


SQL> drop table t1 cascade constraints purge;

Table dropped.

SQL>
SQL> create table t1 (ID NUMBER(5), tp char(1), nm varchar2(5), st number(2), en number(2) );

Table created.

SQL>
SQL>   insert into t1 values (1,'A', 'a', 0, 5);

1 row created.

SQL>   insert into t1 values (2,'B', 'b', 6, 9);

1 row created.

SQL>   insert into t1 values (3,'C', 'c', 10, 14);

1 row created.

SQL>   insert into t1 values (4,'D', 'd', 15, 20);

1 row created.

SQL>   insert into t1 values (5,'A', 'a', 0, 5);

1 row created.

SQL>   insert into t1 values (6,'B', 'b', 6, 15);

1 row created.

SQL>   insert into t1 values (7,'C', 'c', 16, 25);

1 row created.

SQL>   insert into t1 values (8,'D', 'd', 26, 35);

1 row created.

SQL>
SQL> SELECT *
  2  FROM   ( select trunc((rownum-1)/4)+1 r, st,en,tp,nm from t1 )
  3  PIVOT (SUM(st) AS st
  4  ,      sum(en) AS en
  5  FOR   (tp,nm) IN (
  6     ('A','a') AS c1,
  7     ('B','b') AS c2,
  8     ('C','c') AS c3,
  9     ('D','d') AS c4
 10     )
 11     );

         R      C1_ST      C1_EN      C2_ST      C2_EN      C3_ST      C3_EN      C4_ST      C4_EN
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          0          5          6          9         10         14         15         20
         2          0          5          6         15         16         25         26         35

2 rows selected.

SQL>


Rating

  (2 ratings)

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

Comments

PIVOT not in 10G

Rajeshwaran, Jeyabal, October 20, 2016 - 2:47 pm UTC

Since this question came up from 10g database, so PIVOT clause doesn't work, the below trick would work for 10g database.

demo@ORA10G> select rn ,
  2     sum(case when tp='A' and nm='a' then st end) st_sum1,
  3     sum(case when tp='A' and nm='a' then en end) en_sum1,
  4     sum(case when tp='B' and nm='b' then st end) st_sum2,
  5     sum(case when tp='B' and nm='b' then en end) en_sum2,
  6     sum(case when tp='C' and nm='c' then st end) st_sum3,
  7     sum(case when tp='C' and nm='c' then en end) en_sum3,
  8     sum(case when tp='D' and nm='d' then st end) st_sum4,
  9     sum(case when tp='D' and nm='d' then en end) en_sum4
 10  from (
 11  select t1.* ,
 12     row_number() over(partition by tp
 13             order by id) rn
 14  from t1
 15       )
 16  group by rn ;

        RN    ST_SUM1    EN_SUM1    ST_SUM2    EN_SUM2    ST_SUM3    EN_SUM3    ST_SUM4    EN_SUM4
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          0          5          6          9         10         14         15         20
         2          0          5          6         15         16         25         26         35

2 rows selected.

demo@ORA10G>

Chris Saxon
October 20, 2016 - 3:56 pm UTC

True. Though the version says 10.1.2; last time I checked this didn't exist!

Pivot solved

Felipe Dal Pizzol, October 20, 2016 - 7:18 pm UTC

Hi friend!

It's very useful to me. I could understand your example and I did some modifications to apply in my reality.

My data could not be summarized (sum nor count..) so, the pivot return the key who aggregate all "rows to cols" and used *MAX*. This make the returned data be "a kind of 1:1". After that, use this as a subquery, without the "key" and that's works fine.

Anyway I did it without pivot too, and the two ways are working as expected.

Fantastic!

Thanks for your help!
Dal Pizzol.
Connor McDonald
October 21, 2016 - 1:39 am UTC

glad we could help
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.