Oracle/SQL - 多条记录合并为一条记录[字符串聚合]

Question

我意识到这是一个荒谬的请求，但我想做的是将多个记录连同一些文字一起拉回到单个列中。

因此，给定这样的表，

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

我希望看到带有列的单个记录，其他列（例如区域）都可以，但是像这样的单个列

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

对如何执行此操作有什么想法吗？我正在运行 Oracle 9i，无法在 PL/SQL 中执行此操作

---

好的，表格式已更改了一点，但想法是相同的

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

所以我发现此代码正在通过列出的链接

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;

当我运行它时，我会看到此输出

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

我的想法只是从另一个选择中提取内部选择，我在其中将 STORECODE 和 STORE_NAME 以及所需的 html 进行连接，如下所示...

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;

虽然我们的前端环境确实接受查询，但当我尝试查看结果时，我得到了错误：资源无效。在查看之前，您可能需要重新创建或修复查询。

我知道该错误可能没有帮助，但是您知道为什么我的版本无法工作吗？

谢谢！

Answer 1

这很恶心，但你可以这样做：

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

Answer 2

你玩过 DBMS_XMLGEN 吗？

Answer 3

您可以在 Oracle 中创建聚合函数，请参阅文档。