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

时间:2021-08-27 10:29:10

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;