Let's say I have a database column 'grade' like this:
|grade|
| 1|
| 2|
| 1|
| 3|
| 4|
| 5|
Is there a non-trivial way in SQL to generate a histogram like this?
|2,1,1,1,1,0|
where 2 means the grade 1 occurs twice, the 1s mean grades {2..5} occur once and 0 means grade 6 does not occur at all.
I don't mind if the histogram is one row per count.
If that matters, the database is SQL Server accessed by a perl CGI through unixODBC/FreeTDS.
EDIT: Thanks for your quick replies! It is okay if non-existing values (like grade 6 in the example above) do not occur as long as I can make out which histogram value belongs to which grade.