incorrect syntax near as when make pivot so How to solve it ?

  • i work on sql server 2012 when implement pivot i get error

    incorrect synatx near as but i don't know what is issue

    this is my code :

    DECLARE @result NVARCHAR(MAX)
    DECLARE @col NVARCHAR(MAX)
    DECLARE @sqldata NVARCHAR(MAX)
    -----------------------
    set @result =
    (

    SELECT SUBSTRING((Select ',['+FeatureName +']' AS [text()]
    FROM extractreports.dbo.allfeatures with(nolock)
    GROUP BY FeatureName,displayorder
    ORDER BY (CASE WHEN displayorder IS NULL THEN 1 ELSE 0 END) asc,displayorder,FeatureName asc
    FOR XML PATH (''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'), 2, 10000) [Columns])

    print @result
    --PRINT @Header
    set @col =( select
    substring(
    (
    Select ', '''+ FeatureName +''' as ['+ FeatureName, +']'

    FROM extractreports.dbo.allfeatures with(nolock)

    GROUP BY FeatureName,displayorder

    ORDER BY (CASE WHEN displayorder IS NULL THEN 1 ELSE 0 END) asc,displayorder,FeatureName asc


    For XML PATH ('')
    ,TYPE).value('(./text())[1]','NVARCHAR(MAX)'), 2, 10000) [Columns])

    print @col


    select @sqldata =CONCAT('
    SELECT * Into ##FinalTable
    FROM extractreports.dbo.allfeatures with(nolock)
    PIVOT(max(Value) FOR [FeatureName] IN ('+@result+')) AS PVTTable
    ',
    N' select ''[NXPPartId]'' as ''NXPPartId'',''[comptitorPartId]'' as ''comptitorPartId'',''[zplid]'' as ''zplid'',''CompetitorPartNumber'' as ''CompetitorPartNumber'' ,''CompetitorCompany'' as ''CompetitorCompany'',''Competitors'' as ''Competitors'',''NXPPartNumber'' as ''NXPPartNumber'',''Cross Grade'' as ''Cross Grade'',''ProductName'' as ''ProductName'',''ExecutionDate'' as ''ExecutionDate'', ' +@col + '
    union all
    SELECT cast([NXPPartId] as Varchar(12)) as NXPPartId,cast([comptitorPartId] as Varchar(12)) as comptitorPartId,cast([zplid] as Varchar(12)) as zplid,[CompetitorPartNumber],[CompetitorCompany],[Competitors],[NXPPartNumber],[CrossGrade],[ProductName],cast(ExecutionDate as Varchar(12)) as ''ExecutionDate'', ' +@result + ' FROM ##FinalTable
    group by
    [NXPPartId],
    [comptitorPartId],
    [zplid],
    [CompetitorPartNumber],
    [CompetitorCompany],
    Competitors,
    [NXPPartNumber],
    [CrossGrade] ,
    [ProductName],
    ExecutionDate
    ')

    print @sqldata
    EXEC (@sqldata)

    sql script printed

    error ig get

    Msg 156, Level 15, State 1, Line 7

    Incorrect syntax near the keyword 'as'.

    print sql

    'Competitor Number of Outputs' as [Competitor Number of Outputs], 'Competitor Output Type' as [Competitor Output Type], 'Competitor Output Voltage' as [Competitor Output Vol

    [Competitor Number of Outputs],[Competitor Output Type],[Competitor Output Voltage],[Competitor Rail-to-Rail],[NXP Acceleration Range],[NXP Capacitance Ratio],[NXP M

    SELECT * Into ##FinalTable

    FROM extractreports.dbo.allfeatures with(nolock)

    PIVOT(max(Value) FOR [FeatureName] IN ([Competitor Automotive],[NXP Automotive],[Competitor Normalized Package Name],[NXP Normalized Package Name],[Competitor Accelerometers Type],[Competitor Amplifier Type],[Competitor Battery Type],[Competitor Diode Type],[Competitor Maxim

    as above text of features not complete so how to solve this issue please ?

  • The simplest method to debug dynamic SQL is PRINT/SELECT it, debug that SQL and then propagate the solution to the SQL that produces the dynamic statement. We can't run your SQL, so we can't do that to see what is generating the error. If you don't understand the generated SQL, then post that as well.

    Also, as a side note, '[' + {Value} + ']' is not safe from injection; if you want to safely inject a value use QUOTENAME. Also, personally, you may find it easier to pivot the data with a Cross Tab/Conditional Aggregation rather than the restrictive PIVOT operator (which can be a little confusing to read as well). Finally, I recommend using sys.sp_executesql @SQL over EXEC(SQL) . The former can be parametrised when you need it, where as the latter can't, so it promotes good, and safer, writing standards.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Just a quick thing from my eyeballing on it - it doesn't appear to me that the query you provided gives the results you indicated.

    @result doesn't contain the "AS" keyword, but @col does.

    First thing I'd do is remove all PRINT statements EXCEPT print @sqldata and make sure that that information looks like what you are expecting.  If it is too long (I think PRINT only allows 8000 characters... might be less), I'd use SELECT and then paste that into something like notepad to review.

  • Mr. Brian Gale wrote:

      If it is too long (I think PRINT only allows 8000 characters... might be less), I'd use SELECT and then paste that into something like notepad to review.

    Just for confirmation, PRINT will "print" up to 8,000 bytes of characters, rather 8,000 characters. So, as @sqldata is defined as an nvarchar(MAX), that would be the first 4,000 characters. For a varcharthis'll normally be 8,000 characters, unless you're using a UTF-8 collation. PRINT (Transact-SQL): Remarks

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply