Rollup master row [message #669586] |
Tue, 01 May 2018 03:01 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Dear All,
I have the following structure that present an entry and its sub entries:
create table test_mainsub
(
entry_id number(2),
main_id number(2),
entry_name varchar2(10)
);
insert all
into TEST_MAINSUB (entry_id, main_id, entry_name ) values (1,1,'main1')
into TEST_MAINSUB (entry_id, main_id, entry_name ) values (1,2,'sub12')
into TEST_MAINSUB (entry_id, main_id, entry_name ) values (1,3,'sub13')
into TEST_MAINSUB (entry_id, main_id, entry_name ) values (2,1,'main2')
into TEST_MAINSUB (entry_id, main_id, entry_name ) values (2,2,'sub22')
into TEST_MAINSUB (entry_id, main_id, entry_name ) values (2,3,'sub23')
into TEST_MAINSUB (entry_id, main_id, entry_name ) values (3,1,'main3')
select * from dual;
When I select all I get:
select * from TEST_MAINSUB;
ENTRY_ID MAIN_ID ENTRY_NAME
1 1 main1
1 2 sub12
1 3 sub13
2 1 main2
2 2 sub22
2 3 sub23
3 1 main3
While I need:
ENTRY_ID MAIN_ID ENTRY_NAME
1 1 main1
2 sub12
3 sub13
2 1 main2
2 sub22
3 sub23
3 1 main3
Thanks,
Ferro
|
|
|
Re: Rollup master row [message #669587 is a reply to message #669586] |
Tue, 01 May 2018 03:40 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Use row_number analytic to identify the rows you want to display entry_id for and case to control the display;
SQL> select case when rn=1 then entry_id end as entry_id,
2 main_id,
3 entry_name
4 from (select entry_id,
5 main_id,
6 entry_name,
7 row_number() over (partition by entry_id order by main_id) rn
8 from test_mainsub
9 );
ENTRY_ID MAIN_ID ENTRY_NAME
---------- ------- ----------
1 1 main1
2 sub12
3 sub13
2 1 main2
2 sub22
3 sub23
3 1 main3
7 rows selected
|
|
|
|
Re: Rollup master row [message #669589 is a reply to message #669586] |
Tue, 01 May 2018 07:14 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Well, if main_id always starts with 1, then:
select case main_id
when 1 then entry_id
end entry_id,
main_id,
entry_name
from test_mainsub t
order by t.entry_id,
main_id
/
ENTRY_ID MAIN_ID ENTRY_NAME
---------- ---------- ----------
1 1 main1
2 sub12
3 sub13
2 1 main2
2 sub22
3 sub23
3 1 main3
7 rows selected.
SQL>
SY.
|
|
|
|