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., Feb 23 '12 at 00:45
possible duplicate of [Fast way to generate concatenated strings in Oracle](http://stackoverflow.com/questions/7531001/fast-way-to-generate-concatenated-strings-in-oracle) — Jon Heller, Feb 23 '12 at 02: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, Feb 23 '12 at 14:12

score 3 · Accepted Answer · edited Feb 22 '12 at 23:16

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 '12 at 23:16

John Doyle

7,475
5
33
40

answered Feb 22 '12 at 23:05

Ben

51,770
36
127
149

1

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. – WW. Feb 23 '12 at 00:46
And just to be more difficult - any thoughts on doing this without using wm_concat() – dscl Feb 23 '12 at 15:03
@dscl, please note I do not condone doing this. Just pointing it out as a solution. You should take [WW's](http://stackoverflow.com/users/14663/ww) comment on board. As you're on 9i I would suggest `stragg`, which I [proved](http://stackoverflow.com/questions/8823509/how-to-concatenate-strings/8825034#8825034) is identically limiting to `wm_concat`, so sorry... Other ways would be worse. – Ben Feb 23 '12 at 19:05

score -2 · Answer 2 · answered Feb 22 '12 at 23:44

-2

Have you played around with DBMS_XMLGEN?

answered Feb 22 '12 at 23:44

Mike McAllister

1,479
2
12
15

Is this really an answer? Shouldn't it be just a comment? – Ollie Feb 23 '12 at 11:52
Would you rather I reworded this as "DBMS_XMLGEN will let you do what you're looking for"? – Mike McAllister Feb 23 '12 at 18:48
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. – Ollie Feb 23 '12 at 19:07

score -3 · Answer 3 · answered Feb 22 '12 at 22:56

-3

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

answered Feb 22 '12 at 22:56

Amir Pashazadeh

7,170
3
39
69

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

3 Answers3