Home » SQL & PL/SQL » SQL & PL/SQL » decode function (10g, 10.2.0.1. windows)
decode function [message #564645] Mon, 27 August 2012 06:10 Go to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Hi all,

I have table structure like below


name 	product
ashish	1
	2
	3
raj	2
	4
ram	1
	3


and my required output should be as below


name	product
ashish	1,2,3
raj	2,4
ram	1,3



can any one help me how to get this through sql ?


SQL> select name, decode(name, 'ashish', product) "ashish", decode(name, 'ram', product) "ram" from 
product group by name,product;

NAME                     ashish        ram
-------------------- ---------- ----------
ashish                        1
ram                                      2
ram
raj
ashish                        2
ashish                        3
raj



please assist me to get the required result.

Thanks
Re: decode function [message #564646 is a reply to message #564645] Mon, 27 August 2012 06:15 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:

BlackSwan wrote on Thu, 25 August 2011 15:46
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/


Michel Cadot wrote on Thu, 25 August 2011 12:38
Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
...


And I'm not sure it is worth to answer you as anyway we will not have a feedback on our final answers.

Regards
Michel
Re: decode function [message #564656 is a reply to message #564645] Mon, 27 August 2012 07:08 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Hi ishika ,

you can achieve that using wm_concat function which does string aggregation, please find below the code and give me your feedback.



SELECT name, wm_concat(product) AS product
FROM   product
GROUP BY name;



Re: decode function [message #564661 is a reply to message #564656] Mon, 27 August 2012 07:19 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear Arif,

Thanks a lot. Its working Smile

Re: decode function [message #564662 is a reply to message #564656] Mon, 27 August 2012 07:19 Go to previous messageGo to next message
bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore
Member
I read somewhere, Oracle doesn't recommend WM_CONCAT. You can go for XMLAGG function (Looks irrelevant but It works. It saved me once before)
Re: decode function [message #564664 is a reply to message #564662] Mon, 27 August 2012 07:22 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

I agree with you bhat.veeresh@gmail.com but OP has not posted the version details of oracle, as michel suggested they should post the version and follow the forum guidelines .
Re: decode function [message #564666 is a reply to message #564664] Mon, 27 August 2012 07:26 Go to previous messageGo to next message
bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore
Member
Yes Arif. Smile
Re: decode function [message #564667 is a reply to message #564666] Mon, 27 August 2012 07:37 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OP posted his version, it is next to the title, 10.2.0.1 and so LISTAGG does not exist.
SYS_CONNECT_BY_PATH is the way to do it in this version, if you don't want to use WM_CONCAT or you didn't install the option.
Another option is to use STRAGG T. Kyte's function.

Regards
Michel
Re: decode function [message #564672 is a reply to message #564667] Mon, 27 August 2012 08:00 Go to previous messageGo to next message
bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore
Member
Michel: I tried XMLAGG once,

SELECT name, 
  RTRIM(XMLAGG(XMLELEMENT(E,product||',')).EXTRACT('//TEXT()'),',') AS PRODUCT_NEW 
FROM table_name GROUP BY name; 


Is it fine to use XMLAGG, as it may not be appropriate for this context. ?

Regards,
Veeresh
Re: decode function [message #564676 is a reply to message #564672] Mon, 27 August 2012 08:18 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is fine if XML is installed and "text()" is in lower case:
SQL> select deptno, RTRIM(XMLAGG(XMLELEMENT(E,ename||',')).EXTRACT('//TEXT()'),',') vals
  2  from emp
  3  group by deptno;
select deptno, RTRIM(XMLAGG(XMLELEMENT(E,ename||',')).EXTRACT('//TEXT()'),',') vals
                                                             *
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00601: Invalid token in: '//TEXT()'
ORA-06512: at "SYS.XMLTYPE", line 111

SQL> select deptno, RTRIM(XMLAGG(XMLELEMENT(E,ename||',')).EXTRACT('//text()'),',') vals
  2  from emp
  3  group by deptno;
    DEPTNO VALS
---------- --------------------------------------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD


Regards
Michel
Re: decode function [message #564679 is a reply to message #564676] Mon, 27 August 2012 08:53 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

thanks
Re: decode function [message #564693 is a reply to message #564679] Mon, 27 August 2012 10:35 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Another available in all editions since 9i:
SQL> with 
  2    data as (
  3      select deptno, ename,
  4             row_number() over (partition by deptno order by ename) rn,
  5             count(*) over (partition by deptno) cnt
  6      from emp
  7    )
  8  select deptno, 
  9         substr(sys_connect_by_path(ename,','),2) emp
 10  from data
 11  where rn = cnt
 12  connect by prior deptno = deptno and prior rn = rn-1
 13  start with rn = 1
 14  order by deptno
 15  /
    DEPTNO EMP
---------- ------------------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

Regards
Michel

[Updated on: Tue, 28 August 2012 01:09]

Report message to a moderator

Re: decode function [message #564718 is a reply to message #564693] Mon, 27 August 2012 22:53 Go to previous messageGo to next message
bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore
Member
Thanks for the clarification.

Regards,
Veeresh
Re: decode function [message #564725 is a reply to message #564693] Tue, 28 August 2012 02:37 Go to previous message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Thanks Michel, its wonderful to have so many options.
Previous Topic: implement package utl_mail issue ...
Next Topic: SQL Table type variable access
Goto Forum:
  


Current Time: Sat Dec 20 23:41:20 CST 2014

Total time taken to generate the page: 0.14077 seconds