Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '.'.

• ramsai1973

  Mr or Mrs. 500

  Points: 564

  More actions

  June 5, 2012 at 12:52 am

  #274993

  Dear All,

  I''m getting an error msg on executing the below given SQL query:

  Msg 102, Level 15, State 1, Line 1

  Incorrect syntax near '.'.

  declare @columns varchar(max)

  declare @convert varchar(max)

  select @columns = stuff(( select distinct'],[' + CAST(wk.WeekStartDate AS varchar(max))

  from UserProductivityMapping INNER JOIN Weeks wk ON UserProductivityMapping.WeekId = wk.WeekId

  for xml path('')), 1, 2,'') + ']'

  set @convert =

  'select * from (select * from UserProductivityMapping) wk.WeekStartDate

  pivot(sum(PercentageProductivity) for wk.WeekStartDate

  in ('+@columns+')) as pivottable'

  --Print @convert

  --select @convert

  execute (@convert)

  Please guide me in resolving this issue.

  Thanks in advance,

  Ram

• Evil Kraig F

  SSC Guru

  Points: 100851

  More actions

  June 5, 2012 at 1:10 am

  #1496922

  We don't have the UserProductivityMapping INNER JOIN Weeks wk...

  Can you copy/paste the result of your print statement? I believe the error's most likely in that, not in your actual statement. The real statement seems fine.

  ---

  - Craig Farrell

  Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

  For better assistance in answering your questions[/url] | Forum Netiquette
  For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
  Twitter: @AnyWayDBA

• ramsai1973

  Mr or Mrs. 500

  Points: 564

  More actions

  June 5, 2012 at 1:41 am

  #1496926

  Hi Craig,

  Print @columns results:

  [2013-01-05],[2013-01-12],[2013-01-19],[2013-01-26]

  Print @convert results:

  select * from (select * from UserProductivityMapping) wk.WeekStartDate

  pivot(sum(PercentageProductivity) for wk.WeekStartDate

  in ([2013-01-05],[2013-01-12],[2013-01-19],[2013-01-26])) as pivottable

  Thanks and regards,

  Ram

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  June 5, 2012 at 3:12 am

  #1496940

  ramsai1973 (6/5/2012)

  ---

  select * from (select * from UserProductivityMapping) wk.WeekStartDate

  pivot(sum(PercentageProductivity) for wk.WeekStartDate

  in ([2013-01-05],[2013-01-12],[2013-01-19],[2013-01-26])) as pivottable

  There's your problem. Table Aliases can't have dots in them. Alias the subquery wk and assuming there's a column WeekStartDate, it should work.

  Also, lose the SELECT * and write the column names out.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass