Query Statement Required

Question

Hi,

I've following table in my Database:

Column_Name	Data_Type
EmployeeID	nvarchar(50)
JobTitle	nvarchar(200)
LeaveType	nvarchar(200)
LeaveCategory	nvarchar(200)
LeavesAvailed	int

and have following data in this table:

EmployeeID	JobTitle	LeaveType	LeaveCategory	LeavesAvailed
Sec1234	Security	Emergency Leave	Paid	2
Sec1234	Security	Sick Leave	Un-Paid	7

Now, I want to write an SQL statement to retrieve result like:

LeaveType	Paid	Un-Paid	TotalAvailed
Emergency Leave	2	0	2
Sick Leave	0	7	7

Kindly guide me that what should I do or how should write the statement to have this result?

Answer 1

Before someone tells you to use the PIVOT keyword, here is one without, which will make you much happier.

SELECT LeaveType,
       SUM(CASE WHEN LeaveCategory = 'Paid' THEN LeavesAvailed ELSE 0 END) AS Paid,
       SUM(CASE WHEN LeaveCategory = 'Un-Paid' THEN LeavesAvailed ELSE 0 END) AS [Un-Paid],
       SUM(LeavesAvailed) AS [TotalAvailed]
FROM   YourTable
GROUP BY LeaveType

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Thank You so much Erland