SQL SERVER / ORACLE 拼接多行记录字段为一个字符串的方法

SQL SERVER / ORACLE 拼接多行记录字段为一个字符串的方法：

1、ORACLE：使用 WMSYS.WM_CONCAT（注意选取的字段增加to_char转换，确保字符编码正确）

附：在做查询结果的逗号拼接的时候直接使用 wm_concat(字段名) ，有时候会碰见，拼接出来的结果时乱码的情况，目前碰到的时在数据库设计的时候字段指定的字段类型为NVARCHAR2，导致拼接的出来的结果时乱码，通过一番搜集，找到了解决办法：

wmsys.wm_concat(to_char(字段名))即可解决该问题,实现行转列的拼接
 

2、SQL SERVER：使用连接字符串方式选取 

方法一：
select @s = @s + ',' + rtrim(ltrim(NAME)) from tableA;  
select substring(@s, 2, len(@s)); 

declare @sql VARCHAR(MAX), @tracetypes VARCHAR(MAX)
set @sql = 'select WMSYS.WM_CONCAT(to_char(TRACETYPE)) TRACETYPES from ( select TRACETYPE from data.COMMONTRACELOG where ID > ' + CONVERT(VARCHAR(50),@c2) + ' group by TRACETYPE order by count(1) desc ) where rownum <= 10' 
SELECT @tracetypes=TRACETYPES FROM OPENQUERY(clinicdb, 'select WMSYS.WM_CONCAT(to_char(TRACETYPE)) TRACETYPES from ( select TRACETYPE from data.COMMONTRACELOG where ID > ' + CONVERT(VARCHAR(50),@c2) + ' group by TRACETYPE order by count(1) desc ) where rownum <= 10')

SELECT TRACETYPE, count(1) FROM OPENQUERY(clinicdb,'select * from data.commontracelog c') c WHERE c.ID > @c2 group by TRACETYPE order by count(1) desc

declare @s varchar(max);
set @s=''; 
select @s = @s + ',' + rtrim(ltrim(TRACETYPE)) from (SELECT TOP 10 TRACETYPE FROM OPENQUERY(clinicdb,'select * from data.commontracelog c') c WHERE c.ID > @c2 group by TRACETYPE order by count(1) desc) t;
select substring(@s, 2, len(@s));

方法二：

select a.* from 
  ( select d.DepartmentName, stuff((select ',' + e.EmployeeName from dbo.tbEmployee e 
    where e.DepartmentID = d.DepartmentID for xml path('')), 1, 1, '') as EmployeeNames
    from dbo.tbDepartment d where d.DepartmentID > -1 
  ) a
where a.EmployeeNames is not null order by a.DepartmentName;