Click here to Skip to main content
15,921,905 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
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:
SQL
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
--'PRINT @MONTHS;

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.
Posted
Updated 26-Jul-12 21:45pm
v2
Comments
Christian Graus 26-Jul-12 19:11pm    
Did you consider telling us which ( causes the error ?
Tejas Vaishnav 27-Jul-12 4:51am    
can you please give a table information which is used in this query

Replace
SQL
FOR DATENAME(month,dbo.ConvertIntToDate(Date_)) as DDate in


with
SQL
FOR  DDate in
 
Share this answer
 
I got the query working, Also I wanted the dates to be based on selected from and to dates. Thanks to all.:
DECLARE @MonthsP NVARCHAR(MAX),@MonthsOut NVARCHAR(MAX),@SQLSTR NVARCHAR(MAX)
set dateformat dmy;
set @MonthsP = ''
set @MonthsOut = ''
SELECT @MonthsP = @MonthsP + '[' + DATENAME(MONTH, DATEADD(MONTH, x.number, '01-01-2012')) + DATENAME(YEAR, DATEADD(MONTH, x.number, '01-01-2012')) + '],' 
FROM master.dbo.spt_values x 
WHERE (x.type = 'P')  AND (x.number <= DATEDIFF(MONTH, '01-01-2012', '01-07-2012'));
SELECT @MonthsP = SUBSTRING(@MonthsP,1,LEN(@MonthsP) - 1 )
print @MonthsP


SELECT @SQLSTR= 
'SELECT p.Code2,p.Name,p.total,'+@MonthsP+'
FROM (
SELECT  DATENAME(MONTH, dbo.ConvertIntToDate(d.Date_))+ CAST(YEAR(dbo.ConvertIntToDate(d.Date_)) as VARCHAR) AS DDate,
d.Amount2 , m.Name, m.Code2,SUM(d.Amount2) OVER (PARTITION BY d.Code) as total 
FROM data d LEFT OUTER JOIN mr000 m ON d.Code = m.MasterID  
Where (d.tags0=52) AND  (m.Type&0x0f = 7)
) AS s
PIVOT ( 
Sum(s.Amount2)
FOR s.DDate IN(' + @MonthsP + ') 
) AS p'
exec sp_executesql @SQLSTR
print @SQLStr
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900