Oracle common column and row

Question

I have this table as a result of another query

STATUS R1 R2 R3 R4 R5 R6 R7 R8 R9 ---------------------------------------------------- ACCEPTED 322 241 278 473 575 595 567 449 605 ADECUACIONES 0 0 0 0 2 0 1 0 50 AET 0 0 2 0 0 0 0 0 11 EXECUTED 0 80 1 18 9 57 34 30 20 IN PROCESS 0 0 0 0 0 4 25 2 112 FREQ 0 55 2 76 25 117 7 73 48 INSTALL 1 4 1 10 5 14 2 13 62 WO INSTALL 9 2 51 24 143 17 15 59 16 WOT VL 0 1 0 0 1 0 0 0 0 OTHER 22 7 20 28 44 30 6 6 109 PROG 1 0 1 0 0 2 3 0 0 PTE PROG 0 5 0 0 0 0 3 19 93 TMX 0 0 0 28 4 8 11 3 14 PROJ 0 1 12 26 13 8 0 2 4 

What i expect is

 STATUS R1 R2 R3 R4 R5 R6 R7 R8 R9 TOTAL ---------------------------------------------------------- ACCEPTED 322 241 278 473 575 595 567 449 605 4105 ADECUACIONES 0 0 0 0 2 0 1 0 50 53 AET 0 0 2 0 0 0 0 0 11 13 EXECUTED 0 80 1 18 9 57 34 30 20 249 IN PROCESS 0 0 0 0 0 4 25 2 112 143 FREQ 0 55 2 76 25 117 7 73 48 403 INSTALL 1 4 1 10 5 14 2 13 62 112 WO INSTALL 9 2 51 24 143 17 15 59 16 336 WOT VL 0 1 0 0 1 0 0 0 0 2 OTHER 22 7 20 28 44 30 6 6 109 272 PROG 1 0 1 0 0 2 3 0 0 7 PTE PROG 0 5 0 0 0 0 3 19 93 120 TMX 0 0 0 28 4 8 11 3 14 68 PROJ 0 1 12 26 13 8 0 2 4 66 TOTAL 355 396 368 683 821 852 674 656 1144 5949 

I played with grouping() and rollup() , but always get duplicate strings and unnecessary values.

Answer 1

If you have problems, the grouping_id function will help you.

(You can choose grouping_id (col), but also grouping_id (col1, col2, col3, etc.))

But your case is simpler.

What is it like:

 drop table fg_test_group; create table fg_test_group (a number, b number, c number, d number); insert into fg_test_group values (1, 2, 3, 4); insert into fg_test_group values (2, 2, 3, 4); insert into fg_test_group values (3, 2, 3, 4); select nvl(to_char(a), 'total') as a , sum(b), sum(c), sum(d), grouping_id(a) from fg_test_group group by rollup (a) ; 

where a is the status in your case.

Answer 2

 CREATE TABLE TEST1 (STATUS VARCHAR2(10), R1 NUMBER, R2 NUMBER, R3 NUMBER); INSERT INTO TEST1 VALUES ('ACCEPTED', 322,241,278); INSERT INTO TEST1 VALUES ('EXECUTED', 0, 80, 1); INSERT INTO TEST1 VALUES ('FREQ', 0, 55, 2); COMMIT; select NVL(TO_CHAR(STATUS), 'total') as STATUS ,SUM(R1) R1, SUM(R2) R2 , SUM(R3) R3, SUM(R1+R2+R3) from TEST1 group by rollup (STATUS) ; STATUS R1 R2 R3 SUM(R1+R2+R3) ACCEPTED 322 241 278 841 EXECUTED 0 80 1 81 FREQ 0 55 2 57 total 322 376 281 979