如何在由任何字符分隔的单个列中显示人员的详细信息

Question

 Empname      Deptname     LeaveType   TotalLeave
---------------------------------------------------
 Andrew       CSE         SickLeave      3     
 George       IT          CasualLeave    1     
 Andrew       CSE         CasualLeave    2
 George       IT           SickLeave     2 

需要的结果，例如

 Empname      Deptname     LeaveType                  TotalLeave
----------------------------------------------------------------
 Andrew       CSE         SickLeave-3,Casual-2           5
 George       IT          CasualLeave-1,Sickleave-2      3  

我的代码：

SELECT   EmployeeDetails.Empname,
         DepartmentDetails.Deptname ,
         LeaveApplication.LeaveType,
         Sum(LeaveApplication.NoOfDays) As TotalLeave 
FROM DepartmentDetails 
Inner JOIN EmployeeDetails on EmployeeDetails.DeptID = DepartmentDetails.DeptID
INNER JOIN LeaveApplication On EmployeeDetails.EmpID = LeaveApplication.EmpID
WHERE     LeaveApplication.LeaveFromDate >='2017-01-01' 
      AND LeaveApplication.LeaveFromDate <='2017-05-31' 
      AND  EmployeeDetails.Status=0 
      AND  LeaveApplication.leavetype not in ('Forgot Access Card','Permission','Work from Home',
                                      'Holiday Allowance/Weekend Allowance','On Duty','Night Shift Allowance') 
      AND LeaveApplication.LeaveStatus<>'Rejected'
GROUP BY     LeaveApplication.EmpID ,
         DepartmentDetails.Deptname,
         EmployeeDetails.Empname,
         LeaveApplication.LeaveType

Answer 1

 ;With cte(Empname,Deptname,LeaveType,TotalLeave)
 AS
 (
 SELECT 'Andrew','CSE','SickLeave'   ,   3 UNION ALL    
 SELECT 'George','IT' ,'CasualLeave' ,   1 UNION ALL    
 SELECT 'Andrew','CSE','CasualLeave' ,   2 UNION ALL
 SELECT 'George','IT' ,'SickLeave'   ,  2 
 )
SELECT Empname
    ,Deptname
    ,LeaveTypeCombine
    ,LeaveCount AS TotalLeave
FROM (
    SELECT *
        ,STUFF((
                SELECT DISTINCT ', ' + LeaveType + '-'
                    ,CAST(TotalLeave AS VARCHAR(10))
                FROM cte i
                WHERE i.Empname = o.Empname
                FOR XML PATH('')
                ), 1, 1, '') AS LeaveTypeCombine
        ,SUM(TotalLeave) OVER (
            PARTITION BY Empname ORDER BY Empname
            ) LeaveCount
        ,ROW_NUMBER() OVER (
            PARTITION BY Empname ORDER BY Empname
            ) AS Seq
    FROM cte o
    ) dt
WHERE Seq = 1

输出

+---------+----------+---------------------------+------------+
| EmpName | Deptname |         LeaveType         | TotalLeave |
+---------+----------+---------------------------+------------+
| Andrew  | CSE      | CasualLeave-2,SickLeave-3 |          5 |
| George  | IT       | CasualLeave-1,SickLeave-2 |          3 |
+---------+----------+---------------------------+------------+

Answer 2

您可以使用stuff和分组依据来获取逗号分隔的值，如下所示：

select EmpName, Deptname, stuff ((
    select ',' + LeaveType+'-'+ cast(TotalLeave as varchar(5)) from #Table1 where EmpName = t.EmpName and Deptname = t.Deptname for xml path('')
    ),1,1,'') as LeaveType,
    Sum(TotalLeave) as TotalLeave
from #Table1 t
group by EmpName, Deptname

输出

+---------+----------+---------------------------+------------+
| EmpName | Deptname |         LeaveType         | TotalLeave |
+---------+----------+---------------------------+------------+
| Andrew  | CSE      | SickLeave-3,CasualLeave-2 |          5 |
| George  | IT       | CasualLeave-1,SickLeave-2 |          3 |
+---------+----------+---------------------------+------------+

您可以按如下方式更改代码：

;With CTE as (
    SELECT   EmployeeDetails.Empname,
             DepartmentDetails.Deptname ,
             LeaveApplication.LeaveType,
             Sum(LeaveApplication.NoOfDays) As TotalLeave 
    FROM DepartmentDetails 
    Inner JOIN EmployeeDetails on EmployeeDetails.DeptID = DepartmentDetails.DeptID
    INNER JOIN LeaveApplication On EmployeeDetails.EmpID = LeaveApplication.EmpID
    WHERE     LeaveApplication.LeaveFromDate >='2017-01-01' 
          AND LeaveApplication.LeaveFromDate <='2017-05-31' 
          AND  EmployeeDetails.Status=0 
          AND  LeaveApplication.leavetype not in ('Forgot Access Card','Permission','Work from Home',
                                          'Holiday Allowance/Weekend Allowance','On Duty','Night Shift Allowance') 
          AND LeaveApplication.LeaveStatus<>'Rejected'
    GROUP BY     LeaveApplication.EmpID ,
             DepartmentDetails.Deptname,
             EmployeeDetails.Empname,
             LeaveApplication.LeaveType
         )
select EmpName, Deptname, stuff ((
    select ',' + LeaveType+'-'+ cast(TotalLeave as varchar(5)) from CTE where EmpName = t.EmpName and Deptname = t.Deptname for xml path('')
    ),1,1,'') as LeaveType,
    Sum(TotalLeave) as TotalLeave
from CTE t
group by EmpName, Deptname