Home » SQL & PL/SQL » SQL & PL/SQL » Alternative of WM_CONCAT (11G)
 Alternative of WM_CONCAT [message #579819] |
Fri, 15 March 2013 13:49  |
 |
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 followsColumn1 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 #579824 is a reply to message #579819] |
Fri, 15 March 2013 14:35  |
 |
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
|
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:02Please 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 #579847 is a reply to message #579825] |
Sat, 16 March 2013 01:26 |
 |
sss111ind
Messages: 636
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 #579862 is a reply to message #579859] |
Sat, 16 March 2013 02:08 |
|
sss111ind
Messages: 636
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 |
|
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
|
|
Goto Forum:
Current Time: Wed May 21 08:52:25 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
