Error - Not a valid identifier

Ramco Ramco

4y840

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

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty4y ago
Accepted answer
Hey use below query instade of execute @query
execute sp_executesql @query
+1
Ramco Ramco4y ago
Hi

CREATE TABLE #temp(Code nvarchar(10),Name nvarchar(50),

Quantity numeric(19,6),itemname nvarchar(50))

Thanks
+1
Muhammad Imran Ansari4y ago
You are using #temp in your query, can you share the structure of #temp table to identify the issue?
+1

CREATE TABLE #temp(Code nvarchar(10),Name nvarchar(50),
Quantity numeric(19,6),itemname nvarchar(50))