Home » SQL & PL/SQL » SQL & PL/SQL » Hierarchical Output (Oracle 11g R2)
Hierarchical Output [message #621306] Tue, 12 August 2014 08:59 Go to next message
javed.khan
Messages: 308
Registered: November 2006
Location: Banglore
Senior Member

Hi Gurus,

I have one example . The first output is what i have and the second output is what is required.
I have tried connect by prior and all other things , was not able to get through that. Any assistance will be highly appreciated.

Col1		Col2
----------------------------
Hilton		Andy	
Hilton		Ruth
Hilton		Kurt
Hilton		Bishop
Marriot		Daisy
Marriot		Lily
Marriot		Brad
Marriot		Leo

Col1		Col2		
--------------------
Hilton		Andy	
		Ruth
		Kurt
		Bishop
Marriot		Daisy
		Lily
		Brad
		Leo


Thanks & Regards
Javed A. Khan

[EDITED by LF: fixed topic title typo]

[Updated on: Wed, 13 August 2014 00:36] by Moderator

Report message to a moderator

Re: Hirarchical Output [message #621314 is a reply to message #621306] Tue, 12 August 2014 09:10 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2368
Registered: May 2013
Location: World Wide on the Web
Senior Member
You can do it using SQL*Plus command.
Re: Hirarchical Output [message #621315 is a reply to message #621314] Tue, 12 August 2014 09:13 Go to previous messageGo to next message
javed.khan
Messages: 308
Registered: November 2006
Location: Banglore
Senior Member

Okay, But can we do that in query which can run on any IDE?
Re: Hirarchical Output [message #621319 is a reply to message #621315] Tue, 12 August 2014 09:31 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2368
Registered: May 2013
Location: World Wide on the Web
Senior Member
I am not thinking of hierarchical query. You are on 11gR2, did you look at LISTAGG? For col2 append chr(10) in it. Can you try and post.

P.S. : I remember exact question posted in the forum, I replied using LISTAGG, and Michel added further using SQL*Plus command.

[Updated on: Tue, 12 August 2014 09:32]

Report message to a moderator

Re: Hirarchical Output [message #621322 is a reply to message #621315] Tue, 12 August 2014 09:50 Go to previous messageGo to next message
Michel Cadot
Messages: 59173
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

javed.khan wrote on Tue, 12 August 2014 16:13
Okay, But can we do that in query which can run on any IDE?


Use LAG function.

Re: Hirarchical Output [message #621323 is a reply to message #621306] Tue, 12 August 2014 09:53 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2033
Registered: January 2010
Senior Member
There is nothing hierarchical in your request:

select  case row_number() over(partition by col1 order by col2)
          when 1 then col1
        end col1,
        col2
  from  t
  order by t.col1,
           col2
/


For example:

with t as (
           select 'Hilton' col1,'Andy' col2 from dual union all
           select 'Hilton','Ruth' from dual union all
           select 'Hilton','Kurt' from dual union all
           select 'Hilton','Bishop' from dual union all
           select 'Marriot','Daisy' from dual union all
           select 'Marriot','Lily' from dual union all
           select 'Marriot','Brad' from dual union all
           select 'Marriot','Leo' from dual
          )
select  case row_number() over(partition by col1 order by col2)
          when 1 then col1
        end col1,
        col2
  from  t
  order by t.col1,
           col2
/

COL1                       COL2
-------------------------- ------
Hilton                     Andy
                           Bishop
                           Kurt
                           Ruth
Marriot                    Brad
                           Daisy
                           Leo
                           Lily

8 rows selected.

SQL>  


SY.

[Updated on: Tue, 12 August 2014 09:55]

Report message to a moderator

Re: Hirarchical Output [message #621778 is a reply to message #621323] Mon, 18 August 2014 04:43 Go to previous messageGo to next message
javed.khan
Messages: 308
Registered: November 2006
Location: Banglore
Senior Member

Yeah i realized that.
Re: Hierarchical Output [message #621781 is a reply to message #621306] Mon, 18 August 2014 05:10 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2368
Registered: May 2013
Location: World Wide on the Web
Senior Member
javed.khan wrote on Tue, 12 August 2014 19:29
The first output is what i have and the second output is what is required.

Col1		Col2
----------------------------
Hilton		Andy	
Hilton		Ruth
Hilton		Kurt
Hilton		Bishop
Marriot		Daisy
Marriot		Lily
Marriot		Brad
Marriot		Leo

Col1		Col2		
--------------------
Hilton		Andy	
		Ruth
		Kurt
		Bishop
Marriot		Daisy
		Lily
		Brad
		Leo




You can do it in SQL*Plus :

SQL> break on col1 nodup;
SQL> with t as (
  2             select 'Hilton' col1,'Andy' col2 from dual union all
  3             select 'Hilton','Ruth' from dual union all
  4             select 'Hilton','Kurt' from dual union all
  5             select 'Hilton','Bishop' from dual union all
  6             select 'Marriot','Daisy' from dual union all
  7             select 'Marriot','Lily' from dual union all
  8             select 'Marriot','Brad' from dual union all
  9             SELECT 'Marriot','Leo' FROM DUAL
 10            )
 11  select col1, col2 from t;

COL1    COL2
------- ------
Hilton  Andy
        Ruth
        Kurt
        Bishop
Marriot Daisy
        Lily
        Brad
        Leo

8 rows selected.



Regards,
Lalit

Edit : Removed skip.

[Updated on: Mon, 18 August 2014 05:13]

Report message to a moderator

Re: Hierarchical Output [message #621783 is a reply to message #621781] Mon, 18 August 2014 05:16 Go to previous messageGo to next message
Michel Cadot
Messages: 59173
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is not the purpose of his topic and you know it:

Lalit Kumar B wrote on Tue, 12 August 2014 16:10
You can do it using SQL*Plus command.


javed.khan wrote on Tue, 12 August 2014 16:13
Okay, But can we do that in query which can run on any IDE?


So why do you insist? OP already told he don't want this (and don't tell me you want to provide another solution as this is NOT another solution, this is an already REJECTED answer).

Re: Hierarchical Output [message #621784 is a reply to message #621783] Mon, 18 August 2014 05:24 Go to previous message
Lalit Kumar B
Messages: 2368
Registered: May 2013
Location: World Wide on the Web
Senior Member
Michel Cadot wrote on Mon, 18 August 2014 15:46

This is not the purpose of his topic and you know it


Sorry, I completely missed to read my own post above. No more parallel tasks, need a coffee break.
Previous Topic: Reading the xml value
Next Topic: Bind Variables
Goto Forum:
  


Current Time: Tue Sep 23 02:04:12 CDT 2014

Total time taken to generate the page: 0.11064 seconds