Tuesday, January 01, 2013 6:44 PM
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.
Tuesday, January 01, 2013 7:15 PM
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.
- Marked As Answer by OldEnthusiast Tuesday, January 01, 2013 7:22 PM
Tuesday, January 01, 2013 7:22 PM
Thanks Tom it worked. I just changed it to:
It is working now.
- Edited by OldEnthusiast Tuesday, January 01, 2013 7:31 PM
Tuesday, January 01, 2013 7:32 PMI 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?
Tuesday, January 01, 2013 7:43 PMModerator
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
- Edited by Naomi NMicrosoft Community Contributor, Moderator Tuesday, January 01, 2013 7:43 PM
Wednesday, January 02, 2013 5:27 PMOh ok. I confused columns with number of rows.