SQL Pivot Function

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.
2008
2009
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])
from inspections

pivot
(count(roadways)
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.
mounty95Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Walter RitzelSenior Software EngineerCommented:
Please send sample data... Could be just for the columns involved in the pivot.
0
mounty95Author Commented:
The data would be:

surveyid            fy                 roadways
1                        2007             superior
2                        2007             adequate
3                        2007             superior
4                        2008             not applicable
5                        2008             superior
6                        2009             superior

Is this what you mean?
0
Mike EghtebasDatabase and Application DeveloperCommented:
@mounty95,

In a pivot output, you have:

-  row headers (at lease one), surveyid, FY
-  aggregation column (data you sum or count) Here I guess you have counts
- spreading column (Colun headers)   -- Superior, Good, Adequate, etc.

Is this a good summation of what you want?
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

mounty95Author Commented:
So the row headers would be the FY.
The aggregate would be the count of surveyid's.
The column headings would be Superior, Good, Adequate, etc.

So with the same data provided it would be:
                  Superior       Good        Adequate       Not Adequate         Poor           Not Applicable
2007              2                  0                 1                            0                        0                      0
2008              1                  0                 0                            0                        0                      1
2009              1                  0                 0                            0                        0                      0
0
Walter RitzelSenior Software EngineerCommented:
Here it is:
SELECT fy, 
[superior], [good], [adequate], [not adequate], [poor], [not applicable]
FROM
(SELECT fy, roadways, 1 as total 
    FROM test) AS SourceTable
PIVOT
(
Count(total)
FOR roadways IN ([superior], [good], [adequate], [not adequate], [poor], [not applicable])
) AS PivotTable;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mounty95Author Commented:
Awesome!!!!  Thank you so much Walter!!!!!!
0
Mike EghtebasDatabase and Application DeveloperCommented:
Select FY,  [Superior], [Good], [Adequate], [Not Adequate], [Poor], [Not Applicable]
From ( SELECT FY  , 1 as qty FROM TableName ) AS D
Pivot (Count(qty) For roadways IN( [Superior], [Good], [Adequate], [Not Adequate], [Poor], [Not Applicable]) As D;

Open in new window


I am testing it now, brb
0
Mike EghtebasDatabase and Application DeveloperCommented:
Well, I was beat. But it was a good exercise form me.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.