Post reply

Dynamic PIVOT HELP

  • February 21, 2014 at 4:52 am

    #282773

    PLEASE could somebody advise on the following:

    HERE IS MY SQL:

    DECLARE @columns NVARCHAR(MAX)

    DECLARE @query AS NVARCHAR(MAX)

    SELECT @columns = STUFF((SELECT distinct ',' + QUOTENAME(rtrim([STAGE-DESC-ALTERNATE]))

    FROM #MI_JOB_STAGES

    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')

    set @query =

    'SELECT [PLACE-REF],[JOB-NO],[JOB-STATUS-MAP],[CONTRACT-CODE],' + @columns + '

    FROM

    #MI_JOB_STAGES

    PIVOT (MAX([STAGE-DATE]) for [STAGE-DESC-ALTERNATE] in ('+ (@columns) +')

    )p'

    EXEC @QUERY

    GO

    Here is the resulting error:

    Msg 203, Level 16, State 2, Line 13

    The name 'SELECT [PLACE-REF],[JOB-NO],[JOB-STATUS-MAP],[CONTRACT-CODE],[99. Job Cancelled],[09. IBS Completed],[05. Referred to AM],[10. IBS Financially Completed],[01.1. No Access/Refused],[08. Actual Finish],[01. IBS Logged],[11. WHQS Compliant],[02. Planned Start],[06. AM authorised],[04. Surveyed],[07. Actual Start],[03. Planned Finish]

    FROM

    #MI_JOB_STAGES

    PIVOT (MAX([STAGE-DATE]) for [STAGE-DESC-ALTERNATE] in ([99. Job Cancelled],[09. IBS Completed],[05. Referred to AM],[10. IBS Financially Completed],[01.1. No Access/Refused],[08. Actual Finish],[01. IBS Logged],[11. WHQS Compliant],[02. Planned Start],[06. AM authorised],[04. Surveyed]' is not a valid identifier.

    As you can see the IN clause stops at "[04. Surveyed]'" and does not include ",[07. Actual Start],[03. Planned Finish] " for some reason.

    I use the NVARCHAR(MAX) so why does it crop off the last bit???

    Any help would be appreciated.

    Regards

  • February 21, 2014 at 8:09 am

    #1691140

    Duplicate post answered in here: http://qa.sqlservercentral.com/Forums/Topic1543925-391-1.aspx

    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

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

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