CODEWITHSUNDEEP
sqlsql-server
Johny Bravo
Johny Bravo

Reputation: 424

Dynamic pivot where clause

I have a dynamic pivot written as below, I need to add a where clause

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX),@uniqId varchar(50);

set @uniqId = 'IN0s3Z0n8z4v'

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.DisplayLabel) 
        FROM [dbo].[CountyCaseUserData] c where UniqueEntryId = @uniqId
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'') 

print @cols
 set @query = 'SELECT ' + @cols + ' 
 from 
        (
            SELECT UserInput, DisplayLabel,row_number() 
                       over (partition by DisplayLabel order by  CCId) AS No
            from [dbo].[CountyCaseUserData] where UniqueEntryId = @uniqId
       ) x
        pivot 
        (
            max(UserInput)
            for DisplayLabel in (' + @cols + ')
        ) p ';


 execute(@query)

But when executing, I get a message

Must declare the scalar variable "@uniqId"

What am I missing here?

Upvotes: 0

Views: 471

Answers (1)

Squirrel
Squirrel

Reputation: 24763

as what others pointed out,

change execute(@query)

to

exec sp_executesql @query, N'@uniqId varchar(50)', @uniqId

Upvotes: 1

Related Questions