The below query gives the error as rrect syntax near '('. :
Can you please help me where I am wrong?
SELECT DDate,COALESCE([February],0) AS [February], COALESCE([August],0) AS [August], COALESCE([April],0) AS [April], COALESCE([December],0) AS [December], COALESCE([September],0) AS [September], COALESCE([January],0) AS [January], COALESCE([October],0) AS [October] FROM
( select DATENAME(month,dbo.ConvertIntToDate(Date_)) as DDate,Amount1 from data where code=1257
) as SourceTable
PIVOT
(
sum(Amount1)
FOR DATENAME(month,dbo.ConvertIntToDate(Date_)) as DDate IN([February],[August],[April],[December],[September],[January],[October])
)
The original query is as below, the above is the print command results of dynamic query.
Original query:
DECLARE @MONTHS NVARCHAR(MAX),@MONTHSIN NVARCHAR(MAX)
SELECT @MONTHS = STUFF((select ',' + quotename(DDate)
FROM (select distinct DATENAME(mm,dbo.ConvertIntToDate(Date_)) as DDate from data) X FOR XML PATH('')) ,1,1,'')
SELECT @MONTHSIN= STUFF((select ', COALESCE(' + quotename(DDate)+ ',0) AS ' + quotename(DDate)
FROM (select distinct DATENAME(mm,dbo.ConvertIntToDate(Date_)) as DDate from data) X FOR XML PATH('') ),1,2,'')
SELECT @MONTHS = SUBSTRING(@MONTHS,1,LEN(@MONTHS)-1)
print @MONTHS;
DECLARE @SQLSTR NVARCHAR(MAX)
SELECT @SQLSTR =
'SELECT DDate,'+ @MONTHSIN + ' FROM
( select DATENAME(month,dbo.ConvertIntToDate(Date_)) as DDate,Amount1 from data where code=1257
) as SourceTable
PIVOT
(
sum(Amount1)
FOR DATENAME(month,dbo.ConvertIntToDate(Date_)) as DDate IN(' + @MONTHS + ')
)'
print @SQLSTR
execute @SQLSTR
I want to pivot the data month wise. Please can anybody help me with this. The months should be generated dynamic based on the data in the table 'data'. Thank you.