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

时间：2022-01-06 02:03:09

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
     END
SELECT DISTINCT [Student]
      ,dbo.FN_Merge([Student]) AS Course
FROM [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;



标签：

相关文章