Dynamic T-Sql Confusion [Resolved]

Posted by Sharpcnet under Sql Server on 11/20/2013 | Points: 10 | Views : 811 | Status : [Member] | Replies : 6
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.




Responses

Posted by: Bandi on: 11/20/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
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 EmpCode, EmpName,' + @cols +' FROM tbl

PIVOT

(

MAX(Days) FOR TypeCode IN (' + @cols +')

)p

order by date desc'

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 11/20/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
the columns that are part of PIVOT wil not available to outet select....
that is the reason you are getting invalid column error

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 11/20/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
If you wish to get TypeCode and days into the output.. you have to write query as follows:


SELECT @query = 'WITH tbl AS 


( SELECT L.EmpCode, E.EmpName, T.TypeName, L.Days, L.Days DaysForOutput, L.Date, T.TypeCode

FROM tblEmpLeaves L

LEFT OUTER JOIN tblEmployee E ON L.EmpCode = E.EmpCode

LEFT OUTER JOIN tblLeaveType T ON L.TypeCode = T.TypeCode

)

SELECT EmpCode, EmpName, TypeName, DaysForOutput, ' + @cols +' FROM tbl


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 11/21/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
If the above solution is worked for you "Mark it as Answer ";
Otherwise let me know the further updates..

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sharpcnet on: 11/21/2013 [Member] Starter | Points: 25

Up
0
Down
Thank you very much. It worked.

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 11/21/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Welcome

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response