Johny Bravo Johny Bravo - 4 months ago 22
SQL Question

Dynamic pivot where clause

I am having 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 after execution, it says "Must declare the scalar variable "@uniqId"
What am I missing here?

Answer

as what others pointed out,

change execute(@query)

to

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