Dynamic PIVOT HELP

Question

Dynamic PIVOT HELP

SQL_Novice23

SSC Veteran

Points: 201
More actions
February 21, 2014 at 4:50 am

#304618

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 it 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

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Replace EXEC @QUERY with PRINT @QUERY and post the resulting message here.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

SQL_Novice23 SSC Veteran Points: 201 More actions · Answer 2

This is what I expected no cropping when I replaced it with PRINT

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],[07. Actual Start],[03. Planned Finish])

)p

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Try EXEC (@QUERY)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

SQL_Novice23 SSC Veteran Points: 201 More actions · Answer 4

That works

Never knew you had to put it in brackets

Thank-You for you advice 🙂