Allowance and Deduction amount set in one row ,against each employee

Question

Below is my query,I want Allowance and Deduction of each employee in one row,employee ID must not be repeated ,as you may see image Employee ID 30007 ,is being repeated. SELECT (M.Emp_ID) as ID , M.EMp_Name as Name , M.EMP_Salary as Salary,D.Designation_Name as Designation, CASE WHEN T.DA_Type_ID = 'Allowance' THEN Sum (ASD.assign_amt) ELSE 0 END AS Allowance, CASE WHEN T.DA_Type_ID = 'Deduction' THEN Sum (ASD.assign_amt) ELSE 0 END AS Deduction, isnull (M.emp_OT,0) as emp_OT ,0 'txtpresent' , 0 'txtabsent' ,0 'txtsalary' ,0 'lbOT_Amount' ,0 'txthour' FROM tbl_Employee_Master M inner join tbl_designation D on D.Designation_ID=M.Designation_ID inner join tbl_assign_Allowance_Deduction ASD on asd.Emp_ID=M.Emp_ID inner join tbl_Type_allowance_Deduction T on T.DA_ID=ASD.DA_ID group by M.Emp_ID,M.EMp_Name,M.EMP_Salary,D.Designation_Name,T.DA_Type_ID,M.emp_OT image link https://ibb.co/6rXt7nz

Priyanka Jain · Answer

Check below query SELECT ID, Name,Salary,Designation,SUM(Allowance) AS Allowance,SUM(Deduction) AS Deduction,emp_OT,txtpresent,txtabsent,txtsalary,lbOT_Amount,txthour FROM ( SELECT (M.Emp_ID) as ID , M.EMp_Name as Name , M.EMP_Salary as Salary,D.Designation_Name as Designation, CASE WHEN T.DA_Type_ID = 'Allowance' THEN Sum(ASD.assign_amt) ELSE 0 END AS Allowance, CASE WHEN T.DA_Type_ID = 'Deduction' THEN Sum(ASD.assign_amt) ELSE 0 END AS Deduction, isnull(M.emp_OT,0) as emp_OT ,0 'txtpresent' , 0 'txtabsent' ,0 'txtsalary' ,0 'lbOT_Amount' ,0 'txthour' FROM #tbl_Employee_Master M inner join #tbl_designation D on D.Designation_ID=M.Designation_ID inner join #tbl_assign_Allowance_Deduction ASD on asd.Emp_ID=M.Emp_ID inner join #tbl_Type_allowance_Deduction T on T.DA_ID=ASD.DA_ID group by M.Emp_ID,M.EMp_Name,M.EMP_Salary,D.Designation_Name,T.DA_Type_ID,M.emp_OT ) as test GROUP BY ID, Name,Salary,Designation,emp_OT,txtpresent,txtabsent,txtsalary,lbOT_Amount,txthour displaying below output

Vinay Talapaneni · Answer

please check revised one, with cte as (SELECT (M.Emp_ID) as ID , M.EMp_Name as Name , M.EMP_Salary as Salary,D.Designation_Name as Designation, CASE WHEN T.DA_Type_ID = 'Allowance' THEN Sum(ASD.assign_amt) ELSE 0 END AS Allowance, CASE WHEN T.DA_Type_ID = 'Deduction' THEN Sum(ASD.assign_amt) ELSE 0 END AS Deduction, isnull(M.emp_OT,0) as emp_OT ,0 'txtpresent', 0 'txtabsent',0 'txtsalary',0 'lbOT_Amount',0'txthour' FROM #tbl_Employee_Master M inner join #tbl_designation D on D.Designation_ID=M.Designation_ID inner join #tbl_assign_Allowance_Deduction ASD on asd.Emp_ID=M.Emp_ID inner join #tbl_Type_allowance_Deduction T on T.DA_ID=ASD.DA_ID group by M.Emp_ID,M.EMp_Name,M.EMP_Salary,D.Designation_Name,T.DA_Type_ID,M.emp_OT )select ID , Name , Salary,Designation, STUFF((select', '+ (cast (Allowance as varchar(max))+':'+ cast(Deduction as varchar(max))) from cte b where b.Id = a.ID for xml path ('') ),1,1,'' ),emp_OT,txtpresent,txtabsent,txtsalary,lbOT_Amount,txthour from cte a group by ID,Name,Salary,Designation,emp_OT,txtpresent,txtabsent,txtsalary,lbOT_Amount,txthour

Akhter HUssain · Answer

Hi Vinay Talapaneni Your provided query is giving error Msg 207, Level 16, State 1, Line 13 Invalid column name 'ID'. Msg 207, Level 16, State 1, Line 13 Invalid column name 'Name'. Msg 207, Level 16, State 1, Line 13 Invalid column name 'Salary'. Msg 207, Level 16, State 1, Line 13 Invalid column name 'Designation'. Msg 207, Level 16, State 1, Line 11 Invalid column name 'ID'. Msg 207, Level 16, State 1, Line 11 Invalid column name 'Name'. Msg 207, Level 16, State 1, Line 11 Invalid column name 'Salary'. Msg 207, Level 16, State 1, Line 11 Invalid column name 'Designation'.

Akhter HUssain · Answer

Create Table #tbl_Designation (Designation_ID int , Designation_Name varchar (50)); CREATE TABLE #tbl_Employee_Master (emp_ID INT ,emp_Name Varchar (50),emp_salary int ,Designation_ID int ,emp_OT int ); CREATE TABLE #tbl_Type_allowance_Deduction (DA_ID INT ,DA_Name Varchar (50), DA_TYPE_ID Varchar (50)); CREATE TABLE #tbl_assign_Allowance_Deduction (assign_ID INT ,EMP_ID int ,DA_ID int , Assign_amt int ,Sal_ID int ); Insert into #tbl_Designation values (1, 'officer' ), (2, 'Executive' ) Insert into #tbl_Employee_Master values (1001, 'Irfan' ,10000,1,20), (1002, 'Khan' ,10500,2,20), (1003, 'Akhter' ,1200,1,20) insert into #tbl_Type_allowance_Deduction values (1, 'Loan' , 'Deduction' ), (2, 'Medical' , 'Allowance' ), (3, 'Rent' , 'Allowance' ), (4, 'Conveyance' , 'Allowance' ), (5, 'Advance' , 'Deduction' ), (6, 'Mobile' , 'Allowance' ) insert into #tbl_assign_Allowance_Deduction values (111,1001,1,500, null ), (112,1001,2,500, null ), (113,1002,3,540, null ), (114,1003,4,200, null ), (115,1003,6,400, null ) Drop table #tbl_designation Drop table #tbl_Type_allowance_Deduction Drop table #tbl_assign_Allowance_Deduction Drop table #tbl_Employee_Master