PIVOT table syntax issue

0
votes

I tried implementing the following SQL code (SQL Server 2008) but keep getting the syntax issue while trying to PIVOT the tables using the similar code in my earlier post and I am stumped.

http://sqlfiddle.com/#!3/100bd/4

Error:

Incorrect syntax near ','.: DECLARE @query AS NVARCHAR(MAX), @colsPivot as NVARCHAR(MAX) select @colsPivot = STUFF((SELECT ',' + quotename('MEM'+ cast(MEMPER_Instance as varchar(10))+''+REPLACE(c.name, 'MEMPER', '')) from MEM_TEMP t cross apply sys.columns as C where C.object_id = object_id('MEM_TEMP') and C.name not in ('MEMPER_Instance','AHS_ID') group by t.MEMPER_Instance, c.name order by t.MEMPER_Instance FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = ' SELECT * into #temp from ( select ahs_id, ''MEM''+cast(MEMPER_instance as varchar(10))+''_''+col col, value from ( select ahs_id, MEMPER_Instance, ,cast (MEMPER_MinValue as float) MinValue ,cast (MEMPER_MaxValue as as float)) MaxValue ,cast (MEMPER_AvgValue as float) AvgValue ,cast (MEMPER_MedianValue as float) MedianValue ,cast (MEMPER_Count as as float) Count from MEM_TEMP ) x unpivot ( value for col in ( MinValue, MaxValue, AvgValue, MedianValue, Count ) ) u ) x1 pivot ( max(value) for col in ('+ @colspivot +') ) p ; select * from #temp' exec(@query)

How can I identify the problem?

2
sql-server-2008pivot
If you are in SMS 2008, Just double click on the error message it will take you straight to the offending comma. – Tony Hopkinson
@tonyHopkinson I tried in SMS 2008 and it points to the line FOR XML PATH(''), TYPE and I don't see anything different – Rodricks

2 Answers

1
votes

you have two commas in a row in one of your select statements

select ahs_id,
       MEMPER_Instance,         
       ,cast (MEMPER_MinValue as float) MinValue

Extra comma before first cast, 2 as as float in your casts, and )) after one cast

select ahs_id,
             MEMPER_Instance,         
             ,cast (MEMPER_MinValue as float) MinValue
             ,cast (MEMPER_MaxValue as as float)) MaxValue
             ,cast (MEMPER_AvgValue as float) AvgValue
             ,cast (MEMPER_MedianValue as float) MedianValue
             ,cast (MEMPER_Count as as float) Count

fix those 4 mistakes and it ran on fiddle

1
votes

There are several errors with your script.

  • you have an extra comma in your subquery after MEMPER_Instance,
  • you have as repeated multiple times in your float conversions

The script should be:

select * from MEM_TEMP;

DECLARE @query  AS NVARCHAR(MAX),
    @colsPivot as  NVARCHAR(MAX)

select @colsPivot = STUFF((SELECT ',' 
                      + quotename('MEM'+ cast(MEMPER_Instance as varchar(10))+'_'+REPLACE(c.name, 'MEMPER_', ''))
                    from MEM_TEMP t cross apply sys.columns as C
                    where C.object_id = object_id('MEM_TEMP') 
                    and C.name not in ('MEMPER_Instance','AHS_ID')
                    group by t.MEMPER_Instance, c.name
                    order by t.MEMPER_Instance 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query 
  = ' SELECT * into ##temp
      from
      (
        select ahs_id,
          ''MEM''+cast(MEMPER_instance as varchar(10))+''_''+col col, 
          value
        from 
        (
          select ahs_id,
             MEMPER_Instance       
             ,cast (MEMPER_MinValue as float) MinValue
             ,cast (MEMPER_MaxValue as float) MaxValue
             ,cast (MEMPER_AvgValue as float) AvgValue
             ,cast (MEMPER_MedianValue as float) MedianValue
             ,cast (MEMPER_Count as float) Count

          from MEM_TEMP
        ) x
        unpivot
        (
          value
          for col in (
            MinValue,
            MaxValue,
            AvgValue,
            MedianValue,
            Count
          )
        ) u 
      ) x1
     pivot
     (
       max(value)
       for col in ('+ @colspivot +')
     ) p ; '


exec(@query)

select *
from ##temp

See SQL Fiddle with Demo