0

I have 3 tables (1. Emp, 2. OnLeave,3.DaysWeeks). Im trying to write a pivot query that displyas empname, and calenderdates as columns)

Im getting following error, when I try to run the pivot query.

1.Unclosed quotation mark after the character string '2016-12-31])) as pvt'.

2.Incorrect syntax near '2016-12-31])) as pvt'

    DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

SELECT @cols = STUFF((SELECT  ',' + QUOTENAME([CalDate]) 
                    FROM DaysWeeks
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SELECT @query = 
'WITH cte AS (
SELECT EmpDays.Employee,EmpDays.CalDate, ISNULL(v.VacationType,1) AS LeaveStatus
FROM dbo.OnLeave v
RIGHT OUTER JOIN
 (SELECT e.EmpID AS Employee, dw.CalDate AS CalDate, dw.DayOfWeekName AS DoWName
  FROM daysweeks dw,
      dbo.Emp e
      WHERE dw.CalYear = 2016 AND dw.monthnumber=1) AS EmpDays
ON
   v.EmpID = EmpDays.Employee
   AND v.StartingDate <= empdays.CalDate 
   AND v.EndingDate >= empdays.CalDate
)
SELECT * FROM cte PIVOT 
(Max(cte.LeaveStatus) FOR cte.CalDate in([' + @cols + '])) as pvt'


EXEC SP_EXECUTESQL @query
Programmer2015
  • 45
  • 7
  • 1
    Simply try to print the value of `@query` and see if it is correct syntax. You can even manually execute contents of `@query` to see what are you missing –  Jan 27 '16 at 07:00
  • yes I tried to run, instead of @cols I passed a date then it is working fine. but my requirement is i need display all the dates of a month=1 needs to be displayed as columns. – Programmer2015 Jan 27 '16 at 07:07
  • Try removing [] from in query as the date are already enclosing in [date]. So your in would be in(' + @cols + '). For more on this refer http://stackoverflow.com/questions/27422109/how-to-pivot-dynamically-with-date-as-column – J-D Jan 27 '16 at 07:24
  • Thanks @J-D; I have one more questions i need filter the records by passing a deptId value (@deptId as smallint Select @deptId=1). But how can implement it? – Programmer2015 Jan 27 '16 at 07:42

1 Answers1

1

Since QUOTENAME is already added here 

 STUFF((SELECT  ',' + QUOTENAME([CalDate])

You don't need [ and ] here, 

 FOR cte.CalDate in([' + @cols + '])) as pvt'

which is wrong. It will make the entire pivot column list to consider as single column

Change it to 

 FOR cte.CalDate in(' + @cols + ')) as pvt'

Update: To filter the records by passing a deptId value (@deptId as smallint =1). Change your query like this

Declare @deptId SMALLINT = 1

......
RIGHT OUTER JOIN
 (SELECT e.EmpID AS Employee, dw.CalDate AS CalDate, dw.DayOfWeekName AS DoWName
  FROM daysweeks dw,
      dbo.Emp e
      WHERE dw.CalYear = 2016 
        AND dw.monthnumber=1 
        AND e.deptId = @deptId ) AS EmpDays
......

EXEC SP_EXECUTESQL @query,N'@deptId SAMLLINT',@deptId = @deptId
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172