How to Append the details of a person having multiple values in a single Row in in sql

Question

Hi I want to append the 5th Column as "Leave",I am getting the below result with this query

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

Here is my query

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

Result Needed Like

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

score 2 · Answer 1 · answered May 23 '17 at 05:23

2

--Try This
    BEGIN TRAN 

    CREATE TABLE #Detail( Empname NVARCHAR(50),Deptname NVARCHAR(50),LeaveType NVARCHAR(100),Leave INT    )
     ------------------------------------------------

            INSERT INTO #Detail
            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
                c.Empname,c.Deptname,
                STUFF((  
                        SELECT  ', ' +  CONVERT(Nvarchar,CP.LeaveType)
                        from  
                        #Detail CP

                  WHERE
                      C.Empname = CP.Empname
                  FOR XML PATH('')), 1, 2, '') LeaveType,sum(Leave)Total_Leave
            FROM
                #Detail C 
            GROUP BY Empname,Deptname
    ROLLBACK TRAN

answered May 23 '17 at 05:23

Alfaiz Ahmed

1,698
1
11
17

While this might answer the question, some explanations in text will make it a better answer. – Zohar Peled May 23 '17 at 05:24
@ZoharPeled Thanks will keep this in mind – Alfaiz Ahmed May 23 '17 at 05:26
Hi Ahmed, Thanks fro the reply.I have the table which is already exists with datas ,so There is no option for me to insert a column into it coz it a client's table. – Alston May 23 '17 at 05:30
Hi Ahmed,I am getting Invalid column in all the Empname,Since I have used 3 tables it is showing error – Alston May 23 '17 at 06:19
Inner JOIN EmployeeDetails on EmployeeDetails.DeptID = DepartmentDetails.DeptID INNER JOIN LeaveApplication On EmployeeDetails.EmpID = LeaveApplication.EmpID – Alston May 23 '17 at 06:19

score 2 · Answer 2 · answered May 23 '17 at 05:26

Use STUFF and SUM built in functions :

 CREATE TABLE #table1(Empname VARCHAR(20), Deptname VARCHAR(20),LeaveType 
         VARCHAR(20),  TotalLeave INT)

 INSERT INTO #table1(Empname , Deptname ,LeaveType ,  TotalLeave)   
 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 ,  STUFF( (SELECT ',' + LeaveType FROM #table1 I2 
WHERE I2.Empname = I1.Empname FOR XML PATH('')),1,1,'') LeaveType , 
      SUM(TotalLeave) TotalLeave
FROM #table1 I1
GROUP BY Empname , Deptname

Hi Mansoor,Thanks for your Reply.I am using 3 table in join statement and You have used I1 and I2 so I am not getting your way.pls — Alston, May 23 '17 at 05:46
@Alston,I used same table in inner and outer.Not two tables. — Mansoor, May 23 '17 at 05:49

How to Append the details of a person having multiple values in a single Row in in sql

2 Answers2