Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,640 questions
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Below is my query ,i want to set allowance and deduction in one row of each employee,employee ID,must not be repeated again,as you may see in image ,that employee ID 30007 is repeating again.
![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][1]
Remove T.DA_Type_ID from the GROUP BY clause.
Tom