wm_concat built in oracle function ascending or descending column

I am using oracle built in wm_concat function for couple of columns in a query. It outputs data in single line.
my query looks like

select s,w(c) as c,w(a) as a from tbl where c in('zzz','yyy','xxx','uuu','vv') group by s

But i need to ascend or descend the data. How can i achieve it. Any ideas, resources, sample code highly appreciated thanks in advance
LVL 7
gudii9Asked:
Who is Participating?
 
sdstuberCommented:
Here's an example of what I need from you...


WITH tbl
     AS (SELECT 222 s, 'lm' c, 'y' a FROM DUAL
         UNION ALL
         SELECT 222, 'pq', 'n' FROM DUAL
         UNION ALL
         SELECT 222, 'tu', 'y' FROM DUAL
         UNION ALL
         SELECT 222, 'rs', 'n' FROM DUAL)
SELECT   s,
         RTRIM(EXTRACT(XMLAGG(XMLELEMENT("x", c || ',') ORDER BY c), '/x/text()').getstringval(),
               ','
              )
             c,
         RTRIM(EXTRACT(XMLAGG(XMLELEMENT("x", a || ',') ORDER BY c), '/x/text()').getstringval(),
               ','
              )
             a
    FROM tbl
GROUP BY s;



of course, modify the WITH to contain whatever data you're using, and replace the query with whatever query you used.
this example produces the data in the order you requested all concatenated together as originally requested

0
 
Dave FordSoftware Developer / Database AdministratorCommented:
Just use an ORDER BY clause:

select s,
       w(c) as c,
       w(a) as a
  from tbl
 where c in('zzz','yyy','xxx','uuu','vv')
 group by s
 ORDER BY s

HTH,
DaveSlash

Open in new window

0
 
sdstuberCommented:
wm_concat isn't a supported function.  I recommend not using it.

if you've got 11g,   LISTAGG is supported and offers more functionality

if you have 9i or 10g then I suggest using stragg as an analytic with a sorted windowing clause
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sdstuberCommented:
or in 10g, use COLLECT aggregate with tbl2str function

source code for stragg and tbl2str are both available on EE

examples of both can be found here...

http://www.experts-exchange.com/Database/Oracle/10.x/Q_26246671.html
0
 
sdstuberCommented:
listagg usage might look like this...


SELECT   s, LISTAGG(c, ',') WITHIN GROUP (ORDER BY c) AS c, LISTAGG(a, ',') WITHIN GROUP (ORDER BY a)
    FROM tbl
   WHERE c IN ('zzz', 'yyy', 'xxx', 'uuu', 'vv')
GROUP BY s

0
 
gudii9Author Commented:
>>Just use an ORDER BY clause:


please attachmenton how i want
Please advise
output.JPG
0
 
sdstuberCommented:
another option,  use XMLAGG

the syntax is a bit larger but it is supported and doesn't require creation of extra options


SELECT   s,
         RTRIM(EXTRACT(XMLAGG(XMLELEMENT("x", c || ',') ORDER BY c), '/x/text()').getstringval(),
               ','
              )
             c,
         RTRIM(EXTRACT(XMLAGG(XMLELEMENT("x", a || ',') ORDER BY a), '/x/text()').getstringval(),
               ','
              )
             a
    FROM tbl
   WHERE c IN ('zzz', 'yyy', 'xxx', 'uuu', 'vv')
GROUP BY s
0
 
sdstuberCommented:
sorry didn't see your picture (btw,  posting text is easier to read)

if you want both orderings to be c,  then

LISTAGG(a, ',') WITHIN GROUP (ORDER BY c)

or

XMLAGG(XMLELEMENT("x", a || ',') ORDER BY c)
0
 
gudii9Author Commented:
i putting the picuretext here
Initial output looks like this			
#	s	c	a
1	222	lm,pq,tu,rs	y,n,y,n
			
			
output after order by on s as you suggested (which is not what I want			
#	s	c	a
1	222	lm,pq,tu,rs	y,n,y,n
			
			
how I want is I want either ascending/descennding on both c and a which are related like			
			
#	s	c	a
1	222	lm,pq,rs,tu	y,n,n,y

Open in new window


i tried

select s,
       w(c) as c,
       w(a) as a
  from tbl
 where c in('zzz','yyy','xxx','uuu','vv')
 group by s
 ORDER BY c

that did not chage output.

0
 
sdstuberCommented:
there's no reason the order by on the query itself would do what you want.
you need to order the aggregation instead.

try any of the things I suggested

don't use wm_concat
0
 
gudii9Author Commented:
when i run

SELECT   s, LISTAGG(c, ',') WITHIN GROUP (ORDER BY c) AS c,
LISTAGG(a, ',') WITHIN GROUP (ORDER BY c)
    FROM tbl
   WHERE c IN ('zzz', 'yyy', 'xxx', 'uuu', 'vv')
GROUP BY s



ORA-00923: FROM keyword not found where expected

Plese adise
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

The ORDER BY clause I recommended will only sort the "rows returned". It will not sort the order of the values in a comma-separated column. Since it appears that only one row is returned, then you'll need to use the recommendations from sdstuber (whose opinions I regard very highly).
0
 
sdstuberCommented:
>>> ORA-00923: FROM keyword not found where expected


do you have 11g?  if not use one of my 9i or 10g suggestions.

if you have 8i or lower you'll need to do something entirely different
0
 
gudii9Author Commented:
>> stragg and tbl2str
Can you please let me know syntax for 10g with  'stragg and tbl2str'
0
 
sdstuberCommented:
the link above has the syntax to create them as well as examples of their usage
0
 
gudii9Author Commented:
i have gone through the link. But i am not clear on how to modify my query accordingly to support stragg and tbl2str  instead of LISTAGG for my oracle 10g

original query:

select s,w(c) as c,w(a) as a from tbl where c in('zzz','yyy','xxx','uuu','vv') group by s

After LISTAGG which is not working on my 10g db server:

SELECT   s, LISTAGG(c, ',') WITHIN GROUP (ORDER BY c) AS c,
LISTAGG(a, ',') WITHIN GROUP (ORDER BY c)
    FROM tbl
   WHERE c IN ('zzz', 'yyy', 'xxx', 'uuu', 'vv')
GROUP BY s




Please advise
0
 
sdstuberCommented:
instead of

LISTAGG(c, ',') WITHIN GROUP (ORDER BY c) AS c


you would use...

tbl2str(CAST(COLLECT(c ORDER BY c) AS vcarray)) AS c
0
 
sdstuberCommented:
and instead of ....

LISTAGG(a, ',') WITHIN GROUP (ORDER BY c)


you would use...

tbl2str(CAST(COLLECT(a ORDER BY c) AS vcarray))
0
 
gudii9Author Commented:
I tried like


SELECT   s, tbl2str(CAST(COLLECT(c ORDER BY c) AS vcarray)) AS c,
tbl2str(CAST(COLLECT(a ORDER BY c) AS vcarray))
 FROM tbl
WHERE c in('zzz','yyy','xxx','uuu','vv')
GROUP BY s

 Now i am getting

ORA-00902: invalid datatype for vcarray.

Please advise
0
 
sdstuberCommented:
did you create the type and function as shown in the link?

if so, what were the results?

if not, do so
0
 
gudii9Author Commented:
No I  have not. Which link, which type, Which function. I am not clear. Please let me know any other easy simple approach.
0
 
sdstuberCommented:
which link?
   I've only posted one above, in http:#37375544

which type?
   the type mentioned in your error message, "vcarray"

which function?
   the function you are trying to use.  tbl2str

All of the code you need to run is in the accepted answer of the link above...
here's a direct link to that post though....

www.experts-exchange.com/Q_26246671.html#32951489

you need to run the create type and the create function.
then you'll be able to use tbl2str



0
 
sdstuberCommented:
similarly, if you want to use stragg you must create the type, type body and function that are also in the link above

but I recommend tbl2str,  it's much simpler for ordered aggregation, and more efficient
0
 
sdstuberCommented:
or....

use the xmlagg syntax I showed above in http:#37375675 and http:#37375698

you don't need to create anything for that,  xmlagg is built in and fully supported
0
 
gudii9Author Commented:

original query:

select s,w(c) as c,w(a) as a from tbl where c in('zzz','yyy','xxx','uuu','vv') group by s

After LISTAGG which is not working on my 10g db server:

SELECT   s, LISTAGG(c, ',') WITHIN GROUP (ORDER BY c) AS c,
LISTAGG(a, ',') WITHIN GROUP (ORDER BY c)
    FROM tbl
   WHERE c IN ('zzz', 'yyy', 'xxx', 'uuu', 'vv')
GROUP BY s

XMLAGG approach query:

SELECT   s,
         RTRIM(EXTRACT(XMLAGG(XMLELEMENT("x", c || ',') ORDER BY c), '/x/text()').getstringval(),
               ','
              )
             c,
         RTRIM(EXTRACT(XMLAGG(XMLELEMENT("x", a || ',') ORDER BY a), '/x/text()').getstringval(),
               ','
              )
             a
    FROM tbl
   WHERE c IN ('zzz', 'yyy', 'xxx', 'uuu', 'vv')
GROUP BY s

How can i modify my original query to work with XMLAGG to make it work. Does it create XML.  Please advise
0
 
sdstuberCommented:
the xmlagg query I posted should be ready to go "as is"  - just run it and try

I wrote that query based on the table and columns you provided in your original query.
if your table or columns names are different, then change those.

yes, it does create xml as intermediate step to do the aggregation, but the results are not xml.
0
 
gudii9Author Commented:

SELECT   s,
         RTRIM(EXTRACT(XMLAGG(XMLELEMENT("x", c || ',') ORDER BY c), '/x/text()').getstringval(),
               ','
              )
             c,
         RTRIM(EXTRACT(XMLAGG(XMLELEMENT("x", a || ',') ORDER BY a), '/x/text()').getstringval(),
               ','
              )
             a
    FROM tbl
   WHERE c IN ('zzz', 'yyy', 'xxx', 'uuu', 'vv')
GROUP BY s


actually it worked fine for column 'c' but column 'a' did not change that also corresponngly need to chage as below

Initial original output looks like this	without any functions		
#	s	c	a
1	222	lm,pq,tu,rs	y,n,y,n
			
How I got with XMLAGG			
#	s	c	a
1	222	lm,pq,rs,tu	y,n,y,n
			
How I want is 			
			
#	s	c	a
1	222	lm,pq,rs,tu	y,n,n,y

Open in new window

'a'column should be y,n,n,y
instead of  'y,n,y,n' corsponing to ''c' column changes.
please advise
0
 
sdstuberCommented:
you skipped post http:#37375698

if you want them both ordered by c,  then both must use ORDER BY c   not ORDER BY a
0
 
sdstuberCommented:
what do you mean by this?


>>> Initial original output looks like this      without any functions


if you didn't use any functions,  how did your c and a strings get aggregated?
0
 
gudii9Author Commented:

>>> Initial original output looks like this      without any functions



Please ignore that statement.



How I got with XMLAGG                  
#      s      c      a
1      222      lm,pq,rs,tu      y,n,y,n
                  
How I want is                   
                  
#      s      c      a
1      222      lm,pq,rs,tu      y,n,n,y

Please advise
0
 
gudii9Author Commented:
How do i make descending order on 'c'

i mean

want to see column 'c' as
tu,rs,pq,lm
intead of
lm,pq,rs,tu

To doublecheck
Please advise
0
 
sdstuberCommented:

ORDER BY c DESC


your sorting of "a"  y,n,n,y  doesn't mean anything to me.   if ORDER BY c  (or order by c desc)  doesn't produce the desired results,  what is your sorting rule?
0
 
gudii9Author Commented:
How I got with XMLAGG			
#	s	c	a
1	222	lm,pq,rs,tu	y,n,y,n
			
How I want is 			
			
#	s	c	a
1	222	lm,pq,rs,tu	y,n,n,y
			

Open in new window


0
 
gudii9Author Commented:
My requirement is all Column 'c' indicators in 'a' column should not change even if I shuffle 'c' column


I mean lm value of 'c' column--->correspond to 'a' column y value(indicator) all the time, similary pq--->n, rs----n,tu--->y


how i got

c	a
lm	y
pq	n
rs	y
tu	y

Open in new window



how i want

c	a
lm	y
pq	n
rs	n
tu	y

Open in new window



I am puttingn vertical coumn just for unerstanding. I still want
'comma separated column in a single line'
with both column values correspnding teach other n similar position

lm,pq,rs,tu      y,n,n,y

lm is in 1st posion so y also be in first posiion
similarly
tu is in 4th posion so y also be in 4th posiion
0
 
gudii9Author Commented:
'lm' is in 1st posion so 'y' also be in first posiion

'pq' is in 2nd posion so 'n' also be in 2nd posiion

'rs' is in 3rd posion so 'n' also be in 3rd posiion

'tu' is in 4th posion so 'y' also be in 4th posiion


like for every value  in a row 'c' corresponding value in column 'a' l in sigle line though
0
 
sdstuberCommented:
how you get   y,n,n,y    2 y's   when you start  with 3 y's  ?
0
 
sdstuberCommented:
I'm going to guess the 3rd "y" was a typo.
this would be easier with sample data provided like this...


WITH tbl
     AS (SELECT 222 s, 'lm' c, 'y' a FROM DUAL
         UNION ALL
         SELECT 222, 'pq', 'n' FROM DUAL
         UNION ALL
         SELECT 222, 'tu', 'y' FROM DUAL
         UNION ALL
         SELECT 222, 'rs', 'n' FROM DUAL)

assuming that's what your sample data looks like then try this...


SELECT   s,
         RTRIM(
             EXTRACT(XMLAGG(XMLELEMENT("x", c || ',') ORDER BY c DESC), '/x/text()').getstringval(),
             ',')
             c,
         RTRIM(
             EXTRACT(XMLAGG(XMLELEMENT("x", a || ',') ORDER BY rn DESC), '/x/text()').getstringval(),
             ',')
             a
    FROM (SELECT t.*, ROW_NUMBER() OVER (PARTITION BY s ORDER BY c) rn
            FROM tbl t)
GROUP BY s
0
 
sdstuberCommented:
if that's not what your sample data looks like, then adjust and repost in the same form
0
 
sdstuberCommented:
the rownumber shouldn't be needed

SELECT   s,
         RTRIM(EXTRACT(XMLAGG(XMLELEMENT("x", c || ',') ORDER BY c desc), '/x/text()').getstringval(),
               ','
              )
             c,
         RTRIM(EXTRACT(XMLAGG(XMLELEMENT("x", a || ',') ORDER BY c desc), '/x/text()').getstringval(),
               ','
              )
             a
    FROM tbl
GROUP BY s ;

produced the results you requested,  sorted in descending order.  You switched your request a couple of times, so I'm not sure what you really want anymore,  but  it's a trivial change, just take out the "desc" if they are backwards
0
 
gudii9Author Commented:
Yes. Third 'y' is typo. Sorry.

How i got is


how I got is 	
c	a
lm	y
pq	n
rs	y
tu	n

Open in new window


How I want is


c	a
lm	y
pq	n
rs	n
tu	y

Open in new window


please advise
0
 
sdstuberCommented:
"how I got is"  - I'm sorry, I'm not trying to pick on a non-native speaker, but I want to be clear.

do you mean this is your input?  that is,  this is the table's data with no modification?  -  if so, use the query provided but remove the DESC

or do you mean, this is what you are currently seeing as results from the query? - if so, what is your table's data?
0
 
gudii9Author Commented:
i mean this

>>>or do you mean, this is what you are currently seeing as results from the query?

0
 
gudii9Author Commented:
>>if so, what is your table's data?

which table data. Please let me know
0
 
sdstuberCommented:
>>>this is what you are currently seeing as results from the query?

ok, that's fine.  

Which query are you using? Before posting, make sure you've made any/all corrections as suggested above.
I can't tell you which ones you might need, you've changed the requirement a few times and have yet to provide sample input data.

Once you're positive you're using the correct query, and that query isn't returning the correct results.  Post the sample data you used.  Don't post the results of an incorrect query.  I don't need to know what doesn't work.  Just post the data you started with and the query you used.  I can then test using the same thing.

Ideally you will give me test data in the same form I gave it to you  as a WITH clause as shown in http:#37377468


>>which table data.

the table you're querying from.


0
 
sdstuberCommented:
alternately, if you don't like the WITH clause.

modify this script so I can recreate the same test scenario you are using...

create table tbl (s number, c varchar2(10), a varchar2(10));
insert into tbl values (222,'lm','y');
insert into tbl values (222,'pq','n');
insert into tbl values (222,'tu','y');
insert into tbl values (222,'rs','n');
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.