CODEWITHSUNDEEP
sqlsql-serverpivot
vanillacoke9191
vanillacoke9191

Reputation: 305

Pivot Table "Incorrect Syntax"

I'm trying to group my sql table together, but I am receiving an "incorrect syntax"

Declare @pet varchar(max)

SET @pet = (select petsName from pets);

select * from pets

AS basedata

Pivot (
 count(PetID)

 for petsName in (' + @pet+ ') <-- error here
)

as pivottable

Why am I receiving an incorrect syntax near @pet?

Thanks

Upvotes: 0

Views: 141

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93724

You need to use dynamic sql for this 

DECLARE @pet VARCHAR(max)

SET @pet = (SELECT ',' + Quotename(petsName) -- Quotename is used to escape illegal characters
            FROM   pets
            FOR xml path('')); -- to concatenate the records 

SET @pet = Stuff(@pet, 1, 1, '') -- to remove the leading comma 

DECLARE @sql NVARCHAR(8000) = ''

SET @sql = '
select * from pets AS basedata
Pivot ( count(PetID)
for petsName in (' + @pet + ') ) as pivottable' 

exec sp_executesql @sql -- to execute the dynamically framed string

Another mistake in your query is, you are trying to assign petsName to @pet variable.

SET @pet = (select petsName from pets);

But a variable can store only one record. So you need to concatenate the records into one single record separated by comma. Then the variable can be used in pivot list

SET @pet = (SELECT ',' + Quotename(petsName)
            FROM   pets
            FOR xml path(''));

Upvotes: 1

Related Questions