Home » SQL & PL/SQL » SQL & PL/SQL » output in a single row
output in a single row [message #246670] Thu, 21 June 2007 11:02 Go to next message
Safeeq.S
Messages: 100
Registered: October 2005
Location: Bangalore
Senior Member

Hi,

I have a base table called 'fnd_flex_value_children_v' where the parent-child value for the flexfields are stored. I have written a query which returns rows from the child node to parent node with their descriptions.

Structure of the table :

Name Null? Type
------------------------------- -------- ----
FLEX_VALUE_SET_ID NOT NULL NUMBER(10)
PARENT_FLEX_VALUE NOT NULL VARCHAR2(60)
FLEX_VALUE NOT NULL VARCHAR2(150)
DESCRIPTION VARCHAR2(240)
SUMMARY_FLAG NOT NULL VARCHAR2(1)

SELECT FLEX_VALUE,
DESCRIPTION,
LEVEL
FROM APPS.FND_FLEX_VALUE_CHILDREN_V
START WITH FLEX_VALUE = 'CHR052'
CONNECT BY PRIOR PARENT_FLEX_VALUE = FLEX_VALUE

The ouput was printed in this way :

FLEX_VALUE DESCRIPTION LEVEL
---------------------------------------------------------------
CHR052 Human Resources Vilvoorde (BE) 1
CHR00A HUMAN RESOURCES VILVOORDE (BE) 2
CBM141 BUS MAN 1 HR BUSINESS PARTNER VILVOOR 3
CBM237 BUS MAN 2 SITE MANAGER VILVOORDE (BE) 4
CBD113 BUS DIR CONSUMER RETAIL BENELUX 5
CBUTOP BU CONSUMER 6
CODIV3 CONSUMER AND RETAIL 7
COEXEL COUNTRY EXEL 8

I want this output to be printed in a single-row like,

Level_1 Level1_Desc Level_2 Level2-Desc ..Level8 desc
CHR052 desc of level-1 CHR00A desc of level2 ..COEXEL itsdesc

Can any one please let me know how to accomplish. I have attached the sample output of the query with this post.


Thanks
Safeeq



Re: output in a single row [message #246672 is a reply to message #246670] Thu, 21 June 2007 11:04 Go to previous messageGo to next message
Safeeq.S
Messages: 100
Registered: October 2005
Location: Bangalore
Senior Member

Sorry, forget to attach the sample output of the query while posting the question.

Please find the attached sample output file.
  • Attachment: Hierarchy.xls
    (Size: 13.50KB, Downloaded 192 times)
Re: output in a single row [message #246675 is a reply to message #246670] Thu, 21 June 2007 11:15 Go to previous message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Break your lines to max 80-100 characters when you format.
Please always post your Oracle version (4 decimals).

Have a look at SYS_CONNECT_BY_PATH function.

Regards
Michel

Previous Topic: External Table - Problem while retrieving the data
Next Topic: Trigger
Goto Forum:
  


Current Time: Mon Dec 05 06:35:44 CST 2016

Total time taken to generate the page: 0.19809 seconds