Creating a comma-separated list in SQL
For some reason I can never understand, one of the most-asked Oracle questions on the Web goes something like this:
I have a table with values as follows:
SQL> SELECT deptno, ename FROM emp ORDER BY deptno, ename;
DEPTNO ENAME
------ ----------
10 CLARK
10 KING
10 MILLER
20 ADAMS
20 FORD
20 JONES
20 SCOTT
20 SMITH
30 ALLEN
30 BLAKE
30 JAMES
30 MARTIN
30 TURNER
30 WARD
14 rows selected.
but I need them in the following less convenient format:
DEPTNO ENAME
------ -----------------------------------------
10 CLARK, KING, MILLER
20 ADAMS, FORD, JONES, SCOTT, SMITH
30 ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD
Various solutions exist, notably variations on Tom Kyte's STRAGG (STRing AGGregate) which uses a user-defined aggregate function (this facility was added in 9i). James Padfield extended this with CONCAT_ALL, which gets around the restriction that user-defined aggregates may only have one argument, and thus allows you to specify an alternative separator character. Ordering the values takes further work.
In 10g, STRAGG appeared in the WMSYS schema (used for the Workspace Management feature) as WM_CONCAT, so you can use something like this out of the box:
SELECT wmsys.wm_concat(dname) departments FROM dept;
DEPARTMENTS
--------------------------------------------------------------------------------
ACCOUNTING,RESEARCH,SALES,OPERATIONS
In 10g, the new COLLECT operator in SQL makes this simpler (see Adrian Billington's 10g New Features for examples, in particular "The COLLECT function in 10g") although you will still need to write a collection-to-string conversion function (often called JOIN() in other languages, probably not a good choice of name in Oracle).
As an alternative, it is possible to use the analytic ROW_NUMBER() function to simulate a hierarchy in the ordered data, and then in an outer query use SYS_CONNECT_BY_PATH (new in 9i) to show that "hierarchy" as one line, and take the last value in each group using the handy KEEP (DENSE_RANK LAST) construction also added in 9i. This does not result in a particularly efficient or elegant query, but at least
- It is self-contained, as no PL/SQL functions or object types are required, and
- The results are ordered.
The following example illustrates the technique using the SCOTT demo table "emp":
SELECT deptno
, LTRIM(SYS_CONNECT_BY_PATH(ename,','))
FROM ( SELECT deptno
, ename
, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS seq
FROM emp )
WHERE connect_by_isleaf = 1
CONNECT BY seq = PRIOR seq +1 AND deptno = PRIOR deptno
START WITH seq = 1;
DEPTNO CONCATENATED
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
3 rows selected.
Another approach involves harnessing the dark power of XML:
SELECT deptno
, RTRIM
( xmlagg (xmlelement (c, ename || ',') order by ename).extract ('//text()')
, ',' ) AS concatenated
FROM emp
GROUP BY deptno;
DEPTNO CONCATENATED
---------- ---------------------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
3 rows selected.
分享到:
相关推荐
介绍了将多行转为字符串的三种方案,并比较了三种方案的执行效率. 1.sys_connect_by_path + start with ... connect by ... prior + 分析函数 2.自定义Function/SP 3.使用 Oracle 10g 内置函数 wmsys.wm_concat
本篇文章将详细探讨Oracle如何实现行转列为列的过程,以及在面对不确定列数时的解决方案。 在传统的SQL查询中,数据通常是以行的形式存储和展示。然而,在某些场景下,我们可能需要将同一类别的数据从多行转换为一...
本篇文章将详细介绍Oracle中实现多行记录合并的几种方法,包括使用SQL函数、集合操作以及自定义函数。 1. **使用SQL函数:** - **`LISTAGG()` 函数:** 这是Oracle 11g及以后版本引入的一个强大的聚合函数,专门...
行转列(PIVOT)操作主要用于将多行数据根据某些字段进行聚合,并将结果转换为列,每个列代表聚合数据的不同范围。例如,如果有一个员工表(emp),其中包含部门号(deptno)、职位(job)和薪水(sal),行转列操作...
总结来说,本示例展示了如何在Oracle存储过程中定义和使用结构化数组,以及如何在Java中调用这些存储过程并解析返回的多行多列数据。通过这种方式,你可以高效地处理复杂的数据结构,同时利用Java的灵活性来处理这些...
表中数据多行转1行多列
Oracle行转列 Oracle行转列是指在Oracle...Oracle行转列操作可以使用多种方法来实现,包括UNION ALL、MODEL、COLLECTION、AGGREGATE FUNCTION、LISTAGG、REGEXP_SPLIT等方法。这些方法可以满足不同的应用场景和需求。
#### 二、固定列数的行转列方法 当待转换的列是固定的时,可以采用`SUM`函数配合`DECODE`函数的方法来实现行转列。 **示例代码:** ```sql SELECT name, SUM(DECODE(km, '语文', cj, 0)) AS "语文", SUM(DECODE...
本文实例讲述了oracle实现多行合并的方法。分享给大家供大家参考。具体分析如下: 在写sql时,经常会有将某列的字段合并起来,比如将某人名下每个月的工资列示,但是每个人只能占一行。 像这种场景,可能用行列转换...
oracle 某列存儲的值是有逗號的字符串,希望通過逗號分隔開來顯示多行。sql,有預期結果,有項目實戰。
Oracle 中实现行转列功能,并使用逗号进行隔开拼接,成为一条数据是指将多行数据合并成一行数据,并用逗号分隔每个字段的值。这种功能在实际应用中非常有用,例如在报表生成、数据分析和数据整合等场景中。 在 ...
DB2 SQL 通过函数(CONCAT/POSSTR/LOCATE)实现行转列,列转行 可以按照标点把多列转换为一行,多行转换为一列
### Oracle行转列聚合函数WMSYS.WM_CONCAT详解 #### 一、概述 在进行数据处理时,经常会遇到需要将多行数据合并为单行的情况,这通常被称为“行转列”。Oracle数据库提供了多种方法来实现这一需求,其中`WMSYS.WM_...
根据提供的信息,本文将详细介绍如何在Oracle数据库中将某个字段的多行记录转换为一行记录的方法。这种方法在处理大量数据时非常有用,特别是在需要将多个值汇总到一个字段中的场景下。 ### 标题理解:“oracle某个...
总结来说,Oracle中的列转行可以通过多种方法实现,包括传统的`DECODE`或`CASE`结合子查询,以及从11g版本开始提供的`PIVOT`功能。选择哪种方法取决于具体的需求和数据规模,以及对性能的考虑。在处理大数据量时,应...
Oracle提供了多种方法来实现这样的转换,这对于数据分析、报表制作以及优化查询性能等场景非常有用。下面我们将深入探讨Oracle中的行列转换技术。 首先,我们有`PIVOT`操作,它是Oracle 11g引入的新特性,用于将行...
在Oracle 9i中,由于`wm_concat()`函数尚未被引入,因此需要采用更复杂的方法来实现多行合并为一行的功能。这里介绍一种使用`sys_connect_by_path()`函数结合`connect by`语法的方法。 **示例代码:** ```sql ...