Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to 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:
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 26-Jul-12 12:01pm
Edited 26-Jul-12 21:45pm
v2
Comments
Christian Graus at 26-Jul-12 19:11pm
   
Did you consider telling us which ( causes the error ?
Tejas_Vaishnav at 27-Jul-12 4:51am
   
can you please give a table information which is used in this query
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Replace
FOR DATENAME(month,dbo.ConvertIntToDate(Date_)) as DDate in
 
with
FOR  DDate in
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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
 
  Permalink  
v2

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 354
1 Richard MacCutchan 320
2 OriginalGriff 255
3 Vinay Mistry 168
4 Jared Sanow 145
0 Sergey Alexandrovich Kryukov 5,939
1 OriginalGriff 4,885
2 CPallini 2,473
3 Abhinav S 1,507
4 Richard MacCutchan 1,437


Advertise | Privacy | Mobile
Web01 | 2.8.140814.1 | Last Updated 11 Aug 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100