There is one issue in your question. How will you join user_detail
and leave tables? Since you have said that the above answer pulls correct results I assume leave_id ~= user_name. I am posting a new answer since the above approach is not the best way and will not work if you have to pull results for multiple users.
The below example uses PIVOTS and executes much faster.
DECLARE @user_detail TABLE (user_name VARCHAR(50), dept_code INT, active BIT)
DECLARE @leave TABLE (user_name VARCHAR(50), no_of_working_days INT, leave_type VARCHAR(50))
INSERT INTO @user_detail VALUES ('Bob', 9 , 1)
INSERT INTO @user_detail VALUES ('Tracy', 9 , 1)
INSERT INTO @user_detail VALUES ('Maria', 9 , 1)
INSERT INTO @leave VALUES ('Bob', 5, 'Sick')
INSERT INTO @leave VALUES ('Bob', 2, 'Vacational')
INSERT INTO @leave VALUES ('Bob', 1, 'Study')
INSERT INTO @leave VALUES ('Tracy', 2, 'Study')
INSERT INTO @leave VALUES ('Maria', 1, 'Sick')
INSERT INTO @leave VALUES ('Maria', 6, 'Vacational')
INSERT INTO @leave VALUES ('Maria', 9, 'Study')
INSERT INTO @leave VALUES ('Tracy', 8, 'Vacational')
SELECT user_name, dept_code,[Sick], [Vacational], [Study]
FROM
(SELECT l.user_name, dept_code, no_of_working_days, leave_type
FROM @leave l
JOIN @user_detail ud ON l.user_name = ud.user_name) AS Source
PIVOT (MAX(no_of_working_days) FOR leave_type IN ([Sick], [Vacational], [Study])) as p
Result
user_name dept_code Sick Vacational Study
Bob 9 5 2 1
Maria 9 1 6 9
Tracy 9 NULL 8 2