Error in query: [..not a valid identifier]

Question

In the below code, the temporary table #Results holds correct set of data. The data looks like this from table #Results:

I want to show column [earnid] as pivot.

IF OBJECT_ID('tempdb..#Results',N'U') IS NOT NULL
DROP TABLE #Results

select empid,earnid,current_amount,current_hours into #Results from test1

declare @cols nvarchar(max)
declare @sql nvarchar(max)

select @Cols = stuff((select ', ' + quotename(EarnId)
from (select distinct top 100 earnid
as EarnId from #Results ORDER BY earnid) X
ORDER BY EarnId FOR XML PATH('')),1,2,'')

set @SQL = 'SELECT * FROM #Results PIVOT (max(empid) FOR earnid IN (' + @Cols +')) pvt'
exec @sql

When executed, it gives error:

The name 'SELECT * FROM #Results PIVOT (max(empid) FOR earnid IN ([401km], [401kt])) pvt' is not a valid identifier.

Answer 1

First you need to change exec @sql to exec (@sql).  If you don't have the parenthesis, SQL assumes the value contained in @sql is the name of a stored procedure.  Since what you have there cannot be a stored proc name, that is why you are getting this error.

But after fixing this, you will still have errors.  You cannot both select empid and use it in the PIVOT clause.

If you give us the result you want from your query (given the sample data you have provided), we should be able to help you achieve that.  Right now, I not sure exactly what you are looking for in the result.

Tom

Answer 2

Thanks Tom it worked. I just changed it to:

execute (@sql)

It is working now.

Answer 3

I just want to know why removing TOP 100 gives error in Pivot query. Why is TOP necessary? What if I want all the records to be returned?

Answer 4

How many distinct values do you have? The table can have only 1024 columns in SQL Server and in some other clients table should have less than that.

In other words, try changing 100 to 1000 and see if it works.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 5

Oh ok. I confused columns with number of rows.