Home » SQL & PL/SQL » SQL & PL/SQL » Alternative of WM_CONCAT (11G)
Alternative of WM_CONCAT [message #579819] Fri, 15 March 2013 13:49 Go to next message
sqlsatya
Messages: 10
Registered: December 2012
Junior Member
Column1      Column2    Column3    Column4
A            B          C          D
A            B          C          D
A            B          C          E

i have to get records as follows
Column1      Column 2    Column3    Column4
A            B           C          D,E  (duplicate D is appearing only once)

For this I wrote following query
SELECT  Column1, Column2,    Column3, Column4
       ,To_String( Cast( Collect( Column4) As Ntt_Varchar2 ) ) As Column4
FROM     Table1 
group by Column1, Column2,    Column3, Column4;

it is returning value as follwos
Column1      Column 2    Column3    Column4
A            B           C          D,D,E  (duplicate D is appearing twice)

I also tried WM_CONCAT(distinct Column4) nut it seems it is not the best option so could you please suggest some alternative

Thanks,
Satya

[EDITED by LF: applied [code] tags]

[Updated on: Fri, 15 March 2013 14:35] by Moderator

Report message to a moderator

Re: Alternative of WM_CONCAT [message #579821 is a reply to message #579819] Fri, 15 March 2013 13:52 Go to previous messageGo to next message
BlackSwan
Messages: 23025
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Alternative of WM_CONCAT [message #579824 is a reply to message #579819] Fri, 15 March 2013 14:35 Go to previous messageGo to next message
Michel Cadot
Messages: 59731
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topics:

BlackSwan wrote on Mon, 31 December 2012 22:40
...
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


BlackSwan wrote on Mon, 31 December 2012 23:02
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
...


With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) 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.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Use SQL*Plus and copy and paste your session, the WHOLE session.

Regards
Michel


Re: Alternative of WM_CONCAT [message #579825 is a reply to message #579824] Fri, 15 March 2013 14:40 Go to previous messageGo to next message
sqlsatya
Messages: 10
Registered: December 2012
Junior Member
i got result..by using custom function ..please discard this.

Thanks,
Satya
Re: Alternative of WM_CONCAT [message #579847 is a reply to message #579825] Sat, 16 March 2013 01:26 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

I think it'll work
SELECT  Column1, Column2,    Column3, Column4
       ,To_String( Cast( Collect( Column4) As Ntt_Varchar2 ) ) As Column4
FROM    (select distinct Column1, Column2,    Column3, Column4 from Table1)
group by Column1, Column2,    Column3, Column4;

Re: Alternative of WM_CONCAT [message #579851 is a reply to message #579847] Sat, 16 March 2013 01:35 Go to previous messageGo to next message
Michel Cadot
Messages: 59731
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You think? Why don't you post something that you are sure, that you have tested?

Regards
Michel
Re: Alternative of WM_CONCAT [message #579855 is a reply to message #579851] Sat, 16 March 2013 01:50 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

Becuase Michel,I don't have Oracle 11g so how can I test.It should work I did no change there just added the distinct to fit the requirement.

Regards,
Nathan
Re: Alternative of WM_CONCAT [message #579859 is a reply to message #579855] Sat, 16 March 2013 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 59731
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And did you try in your version at least?

Regards
Michel
Re: Alternative of WM_CONCAT [message #579862 is a reply to message #579859] Sat, 16 March 2013 02:08 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

Michel,I have tried in Oracle 10g. It is showing invalid datatype
with temp as(select 'a' col1 ,'b' col2,'c' col3,'d' col4 from dual union all
select 'a' col1 ,'b' col2,'c' col3,'d' col4 from dual union all
select 'a' col1 ,'b' col2,'c' col3,'d' col4 from dual union all
select 'a' col1 ,'b' col2,'c' col3,'e' col4 from dual)
select col1,col2,col3,col4,to_string( cast( collect( col4) as ntt_varchar2 ) ) from temp
group by Col1, Col2,    Col3, Col4;

Regards,
Nathan
Re: Alternative of WM_CONCAT [message #579864 is a reply to message #579862] Sat, 16 March 2013 02:20 Go to previous messageGo to next message
Michel Cadot
Messages: 59731
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't you think it may be a custom type? As well as TO_STRING function?
SQL> with temp as(select 'a' col1 ,'b' col2,'c' col3,'d' col4 from dual union all
  2  select 'a' col1 ,'b' col2,'c' col3,'d' col4 from dual union all
  3  select 'a' col1 ,'b' col2,'c' col3,'d' col4 from dual union all
  4  select 'a' col1 ,'b' col2,'c' col3,'e' col4 from dual)
  5  select col1,col2,col3,col4,to_string( cast( collect( col4) as ntt_varchar2 ) ) from temp
  6  group by Col1, Col2,    Col3, Col4;
select col1,col2,col3,col4,to_string( cast( collect( col4) as ntt_varchar2 ) ) from temp
                                                              *
ERROR at line 5:
ORA-00902: invalid datatype


Note: (almost) all types and packages are described in PL/SQL Packages and Types Reference.

Thinking about the meaning of the query, I'd infer this is an array of strings, so a thing similar to SYS.ODCIVARCHAR2LIST but I'm not sure as I can't be sure of the data types of the columns:
SQL> with temp as(select 'a' col1 ,'b' col2,'c' col3,'d' col4 from dual union all
  2  select 'a' col1 ,'b' col2,'c' col3,'d' col4 from dual union all
  3  select 'a' col1 ,'b' col2,'c' col3,'d' col4 from dual union all
  4  select 'a' col1 ,'b' col2,'c' col3,'e' col4 from dual)
  5  select col1,col2,col3,col4,to_string( cast( collect( col4) as sys.odcivarchar2list))
  6  from temp
  7  /
select col1,col2,col3,col4,to_string( cast( collect( col4) as sys.odcivarchar2list))
                           *
ERROR at line 5:
ORA-00904: "TO_STRING": invalid identifier

Now the problem is to know what is TO_STRING?

This is why I asked for a test case we can reproduce.
So refrain to answer to question you have not all information and to post something you didn't (and can't) verify.

Regards
Michel
Re: Alternative of WM_CONCAT [message #579865 is a reply to message #579819] Sat, 16 March 2013 02:31 Go to previous messageGo to next message
Michel Cadot
Messages: 59731
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In 11g, you have the LISTAGG function:
SQL> with temp as(select 'a' col1 ,'b' col2,'c' col3,'d' col4 from dual union all
  2  select 'a' col1 ,'b' col2,'c' col3,'d' col4 from dual union all
  3  select 'a' col1 ,'b' col2,'c' col3,'d' col4 from dual union all
  4  select 'a' col1 ,'b' col2,'c' col3,'e' col4 from dual)
  5  select col1,col2,col3,
  6         listagg(col4, ',')  within group (order by col4) col4
  7  from (select distinct col1, col2, col3, col4 from temp)
  8  group by Col1, Col2, Col3;
C C C COL4
- - - ----------
a b c d,e

Regards
Michel
icon12.gif  Re: Alternative of WM_CONCAT [message #579866 is a reply to message #579864] Sat, 16 March 2013 02:36 Go to previous message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

Thanks Michel,You opened up my eyes.I'll never touch anything what I don't know. Cool

[Updated on: Sat, 16 March 2013 02:36]

Report message to a moderator

Previous Topic: alternate to like operator
Next Topic: Query Help anyone
Goto Forum:
  


Current Time: Thu Nov 20 17:47:44 CST 2014

Total time taken to generate the page: 0.09187 seconds