Here's the query:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
SELECT @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(T.TypeCode)
FROM tblEmpLeaves L
JOIN tblLeaveType T ON L.TypeId = T.TypeId
FOR XML PATH(''), TYPE
).value('.','NVARCHAR(MAX)'),1,1,'');
SELECT @query = 'WITH tbl AS
( SELECT L.EmpCode, E.EmpName, T.TypeName, L.Days, L.Date
FROM tblEmpLeaves L
LEFT OUTER JOIN tblEmployee E ON L.EmpCode = E.EmpCode
LEFT OUTER JOIN tblLeaveType T ON L.TypeCode = T.TypeCode
)
SELECT * FROM tbl
PIVOT
(
MAX(Days) FOR TypeCode IN (' + @cols +')
)p
order by date desc'
EXECUTE sp_Executesql @query
Here's the problem:
When I say "Select * from tbl", it works fine.
When I say, "Select EmpCode, EmpName from tbl", it works fine then too, but
If I say, "Select EmpCode, EmpName, TypeName, Days from tbl", it gives an error -
'Invalid column names TypeCode , Days.
Why does it happen so. All I want to do is sort by date desc but do not want the date column in the output. The query works fine otherwise.