使用分组依据进行串联 [英] concatenation using group by

问题描述

大家好，

我有一张桌子

EmpCode备注
May001 CL紧急工作
May001 EL假日
May001 EL每周休息
May001 ML Mariage
May001 PL回家
May001 PL不舒服


我需要像
这样的结果
EmpCode备注
May001 CL紧急工作
May001 EL假期+每周休息
May001 ML Mariage
May001 PL回家+感觉不舒服

假设我的桌子上有很多员工，并且我需要将empcode的注释连接起来并明智地离开，即一个员工和一个假期只有一行.
我不想使用局部变量明智的循环和更新.

在此先感谢

Hi all,

I have a table like

EmpCode Leave Remark
May001 CL Urgent Work
May001 EL Holiday
May001 EL Weekly Off
May001 ML Mariage
May001 PL Going to Home
May001 PL Not feeling well


I need the result like

EmpCode Leave Remark
May001 CL Urgent Work
May001 EL Holiday + Weekly Off
May001 ML Mariage
May001 PL Going to Home + Not feeling well

Suppose that i have a lot of employees in table and i need to concatenate the remark for empcode and leave wise i.e. a employee and a leave have only one row.
I do not want to use local variable wise loop and update.

Thanks in advance

推荐答案

尝试一下

Selcet Leave,

(
SELECT SUBSTRING(
(
SELECT '+' + T2.Remark
FROM EmpTable T2
where T2.Leave=T1.Leave
FOR XML PATH('')),2,200000) AS Remark
) as Remark

from EmpTable as T1
Group by Leave

尝试此操作.

try this.

Select distinct Empcode,Leave,
(
SELECT SUBSTRING(
(
SELECT '+' + T2.Remark
FROM EMptest T2
where T2.Leave=T1.leave and T2.Empcode=T1.EmpCode
FOR XML PATH('')),2,200000) AS Remark
) as Remark
from EMptest as T1

这篇关于使用分组依据进行串联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！