Ramco Ramco

Ramco Ramco

  • 475
  • 2.6k
  • 376.5k

Error - Not a valid identifier

Apr 25 2022 4:12 AM

Hi

I am getting below error.

Msg 203, Level 16, State 2, Line 20

The name 'select Name, [Item1],[Item2]

from (select name,quantity,itemname from #temp) x pivot (Sum(quantity) for itemname in ([Item1],[Item2])) piv ' is not a valid identifier.                                                                          

declare @cols as nvarchar(max)='';
declare @query as nvarchar(max)='';
select @cols = @cols + QUOTENAME(ItemName) + ',' from (Select distinct ItemName from #temp) as tmp
select @cols = substring(@cols,0,len(@cols)) set @query = 'select Name, ' + @cols + '
from (select name,quantity,itemname from #temp) x pivot (Sum(quantity) for itemname in (' + @cols + ')) piv ';
execute @query

Thanks


Answers (3)