Home » SQL & PL/SQL » SQL & PL/SQL » show row level records in single column in hirarchy (10g )
show row level records in single column in hirarchy [message #393497] Mon, 23 March 2009 02:07 Go to next message
subusona
Messages: 70
Registered: March 2005
Location: delhi
Member
show row level records in single column in hirarchy

select PRNT_ACCNT_NAME,REGION_NAME,DLR_NAME from xy
order by 1,2,3

PRNT_ACCNT_NAME	           REGION_NAME	       DLR_NAME
------------------------------------------------------------
Group 1 	   Central	       South Pointe CJD.
Group 1 	   Central	       South Pointe 
Group 1 	   Central	       Sterling McCall 
Group 1 	   North East	       Toyota 
Group 1 	   North East	       Toyota xzw
Group 1 	   South East	       World Ford Pines.
Group 1 	   South East	       World Toyota
Group 1 	   West	               Mercedes Benz 



Group 1 
 Central
  South Pointe CJD.
  South Pointe
  Sterling McCall
 North East
  Toyota 
  Toyota xzw
 South East
  World Ford Pines.
  World Toyota
 West
  Mercedes Benz

[Updated on: Mon, 23 March 2009 02:16] by Moderator

Report message to a moderator

Re: show row level records in single column in hirarchy [message #393502 is a reply to message #393497] Mon, 23 March 2009 02:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From one one your previous posts:
Michel Cadot wrote on Thu, 16 October 2008 10:47
Thanks for the test case but please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

...
Regards
Michel



select col1||'
'||col2||'
'||col3
from mytable
/
Re: show row level records in single column in hirarchy [message #393550 is a reply to message #393497] Mon, 23 March 2009 05:42 Go to previous messageGo to next message
subusona
Messages: 70
Registered: March 2005
Location: delhi
Member


Want the output as

Group 1
Central
South Pointe CJD.
South Pointe
Sterling McCall
North East
Toyota
Toyota xzw
South East
World Ford Pines.
World Toyota
West
Mercedes Benz
Re: show row level records in single column in hirarchy [message #393558 is a reply to message #393550] Mon, 23 March 2009 06:30 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I added code tags to your first post, so you should have been able to see the difference between what you posted and what it looks like with code-tags.
Please use code-tags while posting stuff you want to preserve format for.
Re: show row level records in single column in hirarchy [message #393565 is a reply to message #393550] Mon, 23 March 2009 07:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still not formatted and unreadable.

Post a Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: show row level records in single column in hirarchy [message #393579 is a reply to message #393550] Mon, 23 March 2009 07:46 Go to previous message
_jum
Messages: 508
Registered: February 2008
Senior Member
You could use GROUP BY ROLLUP with DECODE like
WITH DATA AS
     (
        SELECT 'Earth' plan, 'Amerika' cont, 'USA' country FROM DUAL
        UNION ALL
        SELECT 'Earth',      'Amerika',      'Canada'      FROM DUAL
        UNION ALL
        SELECT 'Earth',      'Amerika',      'Mexico'      FROM DUAL
        UNION ALL
        SELECT 'Earth',      'Europe',       'Germany'     FROM DUAL
        UNION ALL
        SELECT 'Earth',      'Europe',       'France'      FROM DUAL
        UNION ALL
        SELECT 'Earth',      'Africa',       'Egypt'       FROM DUAL
        UNION ALL
        SELECT 'Earth',      'Africa',       'Tunisia'     FROM DUAL
        UNION ALL
        SELECT 'Earth',      'Asia',         'China'       FROM DUAL)
SELECT   DECODE(cont, NULL, plan) p1, DECODE(country, NULL, cont) p2,country
    FROM DATA
GROUP BY plan, ROLLUP(cont, country)
ORDER BY cont NULLS FIRST, country NULLS FIRST

gives
P1    P2      COUNTRY
----- ------- -------
Earth                
      Africa         
              Egypt  
              Tunisia
      Amerika        
              Canada 
              Mexico 
              USA    
      Asia           
              China  
      Europe         
              France 
              Germany
Previous Topic: drop bitmap indexes
Next Topic: Tune PLSQL block
Goto Forum:
  


Current Time: Sun Dec 04 14:50:44 CST 2016

Total time taken to generate the page: 0.25430 seconds