Home » SQL & PL/SQL » SQL & PL/SQL » comma separated values (10.2.0.2.0)
comma separated values [message #391648] Thu, 12 March 2009 16:01 Go to next message
varunvir
Messages: 363
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,
Re: comma separated values [message #391651 is a reply to message #391648] Thu, 12 March 2009 16:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just searching for pivot.

Regards
Michel
Re: comma separated values [message #391703 is a reply to message #391648] Fri, 13 March 2009 02:16 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hi,

This would help you...

http://www.sqlsnippets.com/en/topic-12291.html
Re: comma separated values [message #391704 is a reply to message #391703] Fri, 13 March 2009 02:22 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Good link, ashoka_bl.

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

MHE
Re: comma separated values [message #391708 is a reply to message #391648] Fri, 13 March 2009 02:25 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Smile Thanks @Maaher,

Ya even i could able to find many links Smile

Re: comma separated values [message #392177 is a reply to message #391708] Mon, 16 March 2009 17:34 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Thanks Ashoka and Maaher for all your help.I am able to generate the query.
Regards,
Varun Punj,
Re: comma separated values [message #392185 is a reply to message #391648] Mon, 16 March 2009 23:13 Go to previous messageGo to next message
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
Re: comma separated values [message #392351 is a reply to message #392185] Tue, 17 March 2009 10:59 Go to previous message
varunvir
Messages: 363
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,
Previous Topic: decode (to_date to_char., default) (merged)
Next Topic: user session is being timed out - no error are recorded
Goto Forum:
  


Current Time: Sat Dec 10 03:09:55 CST 2016

Total time taken to generate the page: 0.09217 seconds