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