Oracle/SQL - Multiple Records Into One [string aggregation]

Question

I realize this is a ridiculous request, but what I'm trying to do is pull multiple records back into a single column along with some literal text.

So given a table like this

REGION  CITY    SID
-------------------
1   Chicago     1234
1   Palatine    567
1   Algonquin   234
1   Wauconda    987

I would like to see back a single record with a column, other columns like region are fine, but a single column like this

<option value="1234">Chicago</option><option value="567">Palatine</option><option value="234">Algonquin</option><option value="987">Wauconda</option>

Any thoughts on how to do this? I'm running Oracle 9i and cannot do this in PL/SQL

Okay the table format has changed a bit, but the idea is the same

COUNTRY STORECODE   STORE_NAME
------------------------------
USA     1234        Chicago
USA     567         Palatine
CAN     987         Toronto

So I found this code going through the links listed

SELECT COUNTRY,
       LTRIM(MAX(SYS_CONNECT_BY_PATH(STORECODE,','))
       KEEP (DENSE_RANK LAST ORDER BY curr),',') AS COUNTRY_HTML
FROM   (SELECT COUNTRY,
               STORECODE,
               ROW_NUMBER() OVER (PARTITION BY COUNTRY ORDER BY STORECODE) AS curr,
               ROW_NUMBER() OVER (PARTITION BY COUNTRY ORDER BY STORECODE) -1 AS prev
        FROM   tablename)
GROUP BY COUNTRY
CONNECT BY prev = PRIOR curr AND COUNTRY = PRIOR COUNTRY
START WITH curr = 1;

And when I run it I see this output

COUNTRY COUNTRY_HTML
--------------------
USA     1234,567
CAN     987

My thought was simply to have the inner select pull from another select where I do my concat of the STORECODE and STORE_NAME along with the html required like this...

SELECT COUNTRY,
       LTRIM(MAX(SYS_CONNECT_BY_PATH(RECORD_HTML,','))
       KEEP (DENSE_RANK LAST ORDER BY curr),',') AS COUNTRY_HTML
FROM   (SELECT COUNTRY,
               RECORD_HTML,
               ROW_NUMBER() OVER (PARTITION BY COUNTRY ORDER BY RECORD_HTML) AS curr,
               ROW_NUMBER() OVER (PARTITION BY COUNTRY ORDER BY RECORD_HTML) -1 AS prev
        FROM   (SELECT COUNTRY, '<option value="' || STORECODE || '">' || STORE_NAME || '</option>' AS RECORD_HTML FROM tablename))
GROUP BY COUNTRY
CONNECT BY prev = PRIOR curr AND COUNTRY = PRIOR COUNTRY
START WITH curr = 1;

While our front end environment does accept the query when I try to review the results I get a error: the resource is invalid. You may need to re-create of fix the query before viewing.

I know that error probably isn't helpful, but any ideas why my version isn't working?

Thanks!

Are you really, really sure you can't do this in your application? — WW.
– WW., Commented Feb 23, 2012 at 0:45
possible duplicate of Fast way to generate concatenated strings in Oracle — Jon Heller
– Jon Heller, Commented Feb 23, 2012 at 2:59
WW. I'm certainly going to push for it, but in the event we cannot or simply as a learning exercise it would be great to be able to do it. — dscl
– dscl, Commented Feb 23, 2012 at 14:12

John Doyle · Accepted Answer · 2012-02-22 23:16:19Z

3

It's disgusting but you could do something like this:

select replace(blah2,',','')
  from ( select wm_concat(blah) as blah2
           from ( select '<option value="' || sid || '">' || city || '</option>' as blah
                    from my_table
                         )
                 )

edited Feb 22, 2012 at 23:16

John Doyle

7,8235 gold badges35 silver badges40 bronze badges

answered Feb 22, 2012 at 23:05

Ben

53.1k36 gold badges133 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

WW. Over a year ago

I know you said it is disgusting, but it is also subject to XML injection. You need to XML-escape sid and city if you take this approach.

dscl Over a year ago

And just to be more difficult - any thoughts on doing this without using wm_concat()

Ben Over a year ago

@dscl, please note I do not condone doing this. Just pointing it out as a solution. You should take WW's comment on board. As you're on 9i I would suggest stragg, which I proved is identically limiting to wm_concat, so sorry... Other ways would be worse.

Mike McAllister · Accepted Answer · 2012-02-22 23:44:27Z

-2

Have you played around with DBMS_XMLGEN?

answered Feb 22, 2012 at 23:44

Mike McAllister

1,5492 gold badges12 silver badges15 bronze badges

3 Comments

Ollie Over a year ago

Is this really an answer? Shouldn't it be just a comment?

Mike McAllister Over a year ago

Would you rather I reworded this as "DBMS_XMLGEN will let you do what you're looking for"?

Ollie Over a year ago

I'm not criticising, just that what you wrote is more of a comment than a hepful answer. Just mentioning DBMS_XMLGEN wouldn't be hugely helpful either, a brief explanation of the specifics would qualify IMHO.

Amir Pashazadeh · Accepted Answer · 2012-02-22 22:56:19Z

-3

You can create an aggregate function in Oracle, see documentations.

answered Feb 22, 2012 at 22:56

Amir Pashazadeh

7,3124 gold badges44 silver badges71 bronze badges

Collectives™ on Stack Overflow

Oracle/SQL - Multiple Records Into One [string aggregation]

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related