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;