SQL/Oracle 将一列多行合并为一行

来源：互联网发布：linux 恢复根目录编辑：程序博客网时间：2024/05/16 19:31

1.SQL

参照：http://www.cnblogs.com/smalleyes/archive/2012/03/08/2385658.html

方法一：用户自定义函数

CREATE FUNCTION FN_Merge (@Student NVARCHAR(50))RETURNS NVARCHAR(50)AS      BEGIN            DECLARE @Course NVARCHAR(50)            SELECT @Course = ISNULL(@Course + ',','') + @Course            FROM SC            WHERE Student = @Student            RETURN @COURSE     ENDSELECT DISTINCT [Student]      ,dbo.FN_Merge([Student]) AS CourseFROM [dbo].[SC]

方法二：FOR XML PATH

SELECT  DISTINCT [Student]      ,STUFF(                        (                              SELECT ','+[Course]                              FROM [dbo].[SC]                              WHERE Student = A.Student                              FOR XML PATH('')                        )                        ,1,1,''                  )AS Course  FROM [dbo].[SC] AS A

myEg:

SELECT  DISTINCT [Student]      ,STUFF(                        (                              SELECT ','+[Course]                              FROM [dbo].[SC]                              WHERE Student = A.Student                              FOR XML PATH('')                        )                        ,1,1,''                  )AS Course  FROM [dbo].[SC] AS A

补充：

sql STUFF用法
（1）作用
删除指定长度的字符，并在指定的起点处插入另一组字符。
（2）语法
STUFF ( character_expression , start , length ,character_expression )
（3）示例
以下示例在第一个字符串 abcdef 中删除从第 2 个位置（字符 b）开始的三个字符，然后在删除的起始位置插入第二个字符串，从而创建并返回一个字符串
SELECT STUFF('abcdef', 2, 3, 'ijklmn')
GO
下面是结果集
aijklmnef
（4）参数
character_expression
一个字符数据表达式。character_expression 可以是常量、变量，也可以是字符列或二进制数据列。
start
一个整数值，指定删除和插入的开始位置。如果 start 或 length 为负，则返回空字符串。如果 start 比第一个 character_expression长，则返回空字符串。start 可以是 bigint 类型。
length
一个整数，指定要删除的字符数。如果 length 比第一个 character_expression长，则最多删除到最后一个 character_expression 中的最后一个字符。length 可以是 bigint 类型。
（5）返回类型
如果 character_expression 是受支持的字符数据类型，则返回字符数据。如果 character_expression 是一个受支持的 binary 数据类型，则返回二进制数据。
（6）备注
如果结果值大于返回类型支持的最大值，则产生错误。

2.Oracle

参照：http://blog.csdn.net/taotao821130/article/details/8939263
方法二：FOR XML PATH
SELECT DISTINCT [Student]
,STUFF(
(
SELECT ','+[Course]
FROM [dbo].[SC]
WHERE Student = A.Student
FOR XML PATH('')
)
,1,1,''
)AS Course
FROM [dbo].[SC] AS A

2.Oracle
oracle 提供了两个函数WMSYS.WM_CONCAT 和 ListAgg函数。
方法一：用函数WMSYS.WM_CONCAT
例：
id name
1 aa
2 bb
3 cc
要的结果是"aa,bb,cc"
（1）SQL:

select WMSYS.WM_CONCAT(a.name) from user a

结果:这样的话，查询出的："aa,bb,cc"
（2）特殊：分隔符如果不需要用英文的逗号，需要改成别的符号比如分号的，可以用下面的方法替换下：
SQL：

select replace(WMSYS.WM_CONCAT(a.name),',','；') from user a

结果："aa;bb;cc"

方法二：用函数ListAgg
（一）listagg函数的语法结构如下：
LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )]
listagg虽然是聚合函数，但可以提供分析功能（比如可选的OVER()子句）。使用listagg中，下列中的元素是必须的：
•需要聚合的列或者表达式
•WITH GROUP 关键词
•分组中的ORDER BY子句
例子：
DEPTNO ENAME
--------- ----------
10 CLARK
10 KING
10 MILLER
20 ADAMS
20 FORD
20 JONES
按照DEPTNO字段分组，对结果集进行字符串聚合，结果如下：
DEPTNO AGGREGATED_ENAMES
--------- -------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES
SQL:

SELECT deptno,LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees FROM  emp GROUP BY deptno;

0 0