February 21, 2014 at 4:52 am
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
Duplicate post answered in here: http://www.sqlservercentral.com/Forums/Topic1543925-391-1.aspx
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply