Our corporate GIS is being upgraded and certain SQL queries need to be simplified to work. We can’t call stored procedures/run dynamic queries or create temporary tables in these queries once we upgrade. The below query creates a pivot table using a dynamic query and I cannot figure out a workaround. It outputs the number of common species within certain cells.

declare @species varchar(20) = 'BIRDS'

declare @cols varchar(max), @pivot varchar(max) set @cols = (select STUFF((SELECT ','+ 
quotename(Common_name) FROM Fauna_Species WHERE Species_Type = @species and cell_ref = 
'@mapkey' ORDER BY Common_Name FOR XML PATH('')),1,1,'')) set @pivot = ' SELECT ' + @cols + 
'FROM  (SELECT Common_nam, count(*) count FROM Fauna_Data_2012 WHERE Species_Ty = 
'''+@species+''' and cell_ref = ''' + '@mapkey' + ''' GROUP BY Common_nam)  AS DP PIVOT 
(MIN([count]) FOR Common_nam IN ('+@cols+' )) AS P;'
exec (@pivot)

As a test I have created a table that holds the Species, Cell Reference and I’ve used STUFF to put all Common_Names for that cell into one field, to be used in the pivot query.

INSERT INTO GIS_Fauna_Fields (Species, CellRef, Common_Name)
SELECT DISTINCT Species_ty, cell_ref, STUFF((SELECT ','+quotename(Common_name)
FROM Fauna_Species f WHERE Species_Type =
Species_ty and f.cell_ref = f2012.cell_ref GROUP BY Common_Name ORDER
BY Common_Name FOR XML PATH('')),1,1,'') FROM Fauna_Data_2012 f2012

Then every time the user in our GIS clicks on a fauna cell the
background query would look something like the below.

DECLARE @cols varchar(max) set @cols = (SELECT Common_Name FROM
GIS_Fauna_Fields WHERE Species = 'BIRDS' and cellref = '150D4') --cellref will be a variable in the GIS configuration

-- pivots results with count
SELECT @cols FROM (SELECT Common_Nam, count(*) [count] FROM Fauna_Data_2012  WHERE Species_Ty = 'BIRDS' and
cell_ref = '150D4' GROUP BY Common_nam)  AS DP PIVOT (MIN([count]) FOR
Common_nam IN (Common_Name)) AS P;

However, I can’t specify the datapoints with a variable so I am using Common_Name. The data will differ for each cell ref but will look something like this

[Common Froglet],[Southern Brown Tree Frog],[Verreauxs Tree Frog]

I am not getting the desired results. I need the column name (Common_Name) containing the count, but I get a no-name column containing the Common_Name instead.

Is there a way around this without dynamic queries?