OraFAQ Forum: SQL & PL/SQL » comma separated values

Home » SQL & PL/SQL » SQL & PL/SQL » comma separated values (10.2.0.2.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

comma separated values [message #391648]

Thu, 12 March 2009 16:01

varunvir
Messages: 389
Registered: November 2007

Senior Member

Hi Experts,
I am executing one queryas:-

SELECT d.doc_id, 
       e.attribute_value 
FROM   ct_bhi_document d, 
       lt_bhi_doc_attribute e 
WHERE  d.oid (+)  = e.oid;

The results of this query are:-

UID163067 Agriculture
UID163067 Australia
UID163068 English
UID163068 crop protection

How can I get the following results:-

UID163067 Agriculture,Australia
UID163068 English,crop protection

Regards,
Varun Punj,

Report message to a moderator

Re: comma separated values [message #391651 is a reply to message #391648]

Thu, 12 March 2009 16:08

Michel Cadot
Messages: 68719
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Just searching for pivot.

Regards
Michel

Report message to a moderator

Re: comma separated values [message #391703 is a reply to message #391648]

Fri, 13 March 2009 02:16

ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore

Senior Member

Hi,

This would help you...

http://www.sqlsnippets.com/en/topic-12291.html

Report message to a moderator

Re: comma separated values [message #391704 is a reply to message #391703]

Fri, 13 March 2009 02:22

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Good link, ashoka_bl.

Here's an old thread on OraFAQ with a couple of examples: link

MHE

Report message to a moderator

Re: comma separated values [message #391708 is a reply to message #391648]

Fri, 13 March 2009 02:25

ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore

Senior Member

Thanks @Maaher,

Ya even i could able to find many links Smile

Report message to a moderator

Re: comma separated values [message #392177 is a reply to message #391708]

Mon, 16 March 2009 17:34

varunvir
Messages: 389
Registered: November 2007

Senior Member

Thanks Ashoka and Maaher for all your help.I am able to generate the query.
Regards,
Varun Punj,

Report message to a moderator

Re: comma separated values [message #392185 is a reply to message #391648]

Mon, 16 March 2009 23:13

ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore

Senior Member

Hi Varun,

Can you post the query that you have tried , so that others here can refer.

Regards,
Ashoka BL
Bengaluru

Report message to a moderator

Re: comma separated values [message #392351 is a reply to message #392185]

Tue, 17 March 2009 10:59

varunvir
Messages: 389
Registered: November 2007

Senior Member

Hi Ashoka,
I have generated the following query:-

SELECT docs.doc_id, 
       child.hierachy 
FROM   (SELECT   ta.oid, 
                 Max(Decode(val_number,1,ta.attribute_name, 
                                       NULL)) 
                 ||Max(Decode(val_number,2,',' 
                                           ||ta.attribute_name, 
                                         NULL)) 
                 ||Max(Decode(val_number,3,',' 
                                           ||ta.attribute_name, 
                                         NULL)) 
                 ||Max(Decode(val_number,4,',' 
                                           ||ta.attribute_name, 
                                         NULL)) 
                 ||Max(Decode(val_number,5,',' 
                                           ||ta.attribute_name, 
                                         NULL)) 
                 ||Max(Decode(val_number,6,',' 
                                           ||ta.attribute_name, 
                                         NULL)) 
                 ||Max(Decode(val_number,7,',' 
                                           ||ta.attribute_name, 
                                         NULL)) 
                 ||Max(Decode(val_number,8,',' 
                                           ||ta.attribute_name, 
                                         NULL)) 
                 ||Max(Decode(val_number,9,',' 
                                           ||ta.attribute_name, 
                                         NULL)) 
                 ||Max(Decode(val_number,10,',' 
                                            ||ta.attribute_name, 
                                         NULL)) 
                 ||Max(Decode(val_number,11,',' 
                                            ||ta.attribute_name, 
                                         NULL)) 
                 ||Max(Decode(val_number,12,',' 
                                            ||ta.attribute_name, 
                                         NULL)) 
                 ||Max(Decode(val_number,13,',' 
                                            ||ta.attribute_name, 
                                         NULL)) 
                 ||Max(Decode(val_number,14,',' 
                                            ||ta.attribute_name, 
                                         NULL)) AS hierachy 
        FROM     (SELECT oid, 
                         Row_number() 
                           OVER(PARTITION BY oid ORDER BY attribute_name) AS val_number, 
                         attribute_name 
                  FROM   (SELECT DISTINCT ta.attribute_name, 
                                          ta.oid 
                          FROM   lt_bhi_doc_attribute ta)) ta 
        GROUP BY ta.oid 
        ORDER BY ta.oid) child, 
       ct_bhi_document docs 
WHERE  docs.oid = child.oid;

Regards,
Varun Punj,

Report message to a moderator

Previous Topic:	decode (to_date to_char., default) (merged)
Next Topic:	user session is being timed out - no error are recorded

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 08 18:57:32 CST 2024