You may have seen something resembling this post in the past days. It's about dynamic pivot and the thread was closed but unanswered, never mind.
But my question here is about a different aspect of the problem.
I finally managed to make dynamic pivot work. See the query below which works perfectly:
DECLARE @listCol VARCHAR(2000) DECLARE @query VARCHAR(4000) SELECT @listCol = STUFF ( ( SELECT DISTINCT '],[' + CAST(month(T0.RefDate) AS varchar) FROM JDT1 T0 FOR XML PATH('') ), 1, 2, ' ') + ']' SET @query = 'SELECT * FROM (SELECT Account, month (T0.RefDate) Month , Debit FROM JDT1 T0 ------stops working if we add the WHERE clause just below this line---------------- GROUP BY Account, RefDate, Debit ) S PIVOT ( Sum(Debit) FOR Month IN ('+@listCol+') ) AS pvt' EXECUTE (@query)
But if I add the foll. WHERE clause just below the dotted lines, it stops working. It appears to be a problem on date formatting.
Can anybody help me convert the date properly?
WHERE T0.RefDate >= '[%1]' and T0.RefDate <= '[%2]'
Error Message is:
Incorrect Syntax near '20110101'
if I input 01.01.11 in Selection Criteria (1 Jan 2011)