Home » SQL & PL/SQL » SQL & PL/SQL » Rollup master row (11.0.2.10)
Rollup master row [message #669586] Tue, 01 May 2018 03:01 Go to next message
OraFerro
Messages: 366
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 Go to previous messageGo to next message
cookiemonster
Messages: 13789
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 #669588 is a reply to message #669587] Tue, 01 May 2018 04:27 Go to previous messageGo to next message
Littlefoot
Messages: 21530
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oh, there's a simpler way

SQL> break on entry_id
SQL> select * from test_mainsub;

  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>

but that's most probably not what OraFerro wants Smile
Re: Rollup master row [message #669589 is a reply to message #669586] Tue, 01 May 2018 07:14 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2910
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.
Re: Rollup master row [message #669593 is a reply to message #669589] Tue, 01 May 2018 23:39 Go to previous message
OraFerro
Messages: 366
Registered: July 2011
Senior Member
Thanks a lot
Previous Topic: how to use variable to store comma seperated values
Next Topic: Stored procedure to load initial bulk load from one DB to another DB
Goto Forum:
  


Current Time: Wed Jan 29 03:13:06 CST 2020