I have a table with 35 columns that capture an inspection rating of Superior, Good, Adequate, Not Adequate, Poor, or Not Applicable per each column. I have a fiscal year column as well. I would like to develop a SQL script that will take each column and build it's own pivot table returning a row per fiscal year with a count of Superior, Good, Adequate, Not Adequate, Poor, and Not Applicable. Something like this:
Superior Good Adequate Not Adequate Poor Not Applicable
2007 count, etc.
so on and so forth.
I developed this for the first column, but the counts are the same for each column for each fy:
select fy,count([Superior]),count([Good]),count([Adequate]),count([Not Adequate]),count([Poor]),count([Not Applicable])
for roadways in
([Superior],[Good],[Adequate],[Not Adequate],[Poor],[Not Applicable])) as pvt
group by fy
order by fy
Any help would be greatly appreciated. Thank you in advance.