Post reply

Incorrect syntax near the keyword 'pivot'.

  • October 8, 2014 at 5:40 pm

    #297199

    Hi All,

    i'm struggling with one Syntax error , any help is appreciate‎

    CREATE TABLE #ToolCompliance

    (

    SOFTWAR_ID INT

    ,CONTROL_CODE VARCHAR(100)

    ,CONTROL_STATUS VARCHAR(100)

    )

    INSERT INTO #ToolCompliance

    VALUES(1000,'AC','SUCCESS')

    ,(1000,'CC','FAILED')

    ,(10000,'VC','SUCCESS')

    ,(10000,'IC','SUCCESS')

    ,(10000,'DC','SUCCESS')

    ,(10000,'BR','FAILED')

    SELECT *

    FROM (

    SELECT

    SOFTWAR_ID

    ,CONTROL_CODE

    ,CONTROL_STATUS

    FROM #ToolCompliance

    ) as s

    PIVOT

    (

    MAX(CONTROL_STATUS)

    FOR [CONTROL_CODE] IN ([AC], [CC], [VC], [IC], [DC], [BR])

    )AS pivot

    DROP TABLE #ToolCompliance

  • October 8, 2014 at 5:44 pm

    #1751126

    The problem is with your pivot alias. You need to use square brackets if you want to use a reserved word or, even better, change the alias into something less problematic.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • October 9, 2014 at 7:05 am

    #1751270

    please try this code it is working fine.

    SELECT *

    FROM (

    SELECT

    SOFTWAR_ID

    ,CONTROL_CODE

    ,CONTROL_STATUS

    FROM #ToolCompliance

    ) as s

    PIVOT

    (

    MAX(CONTROL_STATUS)

    FOR [CONTROL_CODE] IN ([AC], [CC], [VC], [IC], [DC], [BR])

    )AS [pivot]

  • October 11, 2014 at 1:00 pm

    #1751826

    Thanks Luis 🙂 , this solved my issue

  • October 11, 2014 at 1:00 pm

    #1751827

    Thanks Bhanu 🙂 , this solved my issue

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

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