| Hierarchical Output [message #621306] |
Tue, 12 August 2014 08:59  |
|
|
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 #621319 is a reply to message #621315] |
Tue, 12 August 2014 09:31  |
Lalit Kumar B
Messages: 3174
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 #621323 is a reply to message #621306] |
Tue, 12 August 2014 09:53 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
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: Hierarchical Output [message #621781 is a reply to message #621306] |
Mon, 18 August 2014 05:10 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
javed.khan wrote on Tue, 12 August 2014 19:29The 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 #621784 is a reply to message #621783] |
Mon, 18 August 2014 05:24 |
Lalit Kumar B
Messages: 3174
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
