Simple histograms in SQL Server

from the Artful SQL Server & Access Tips List

(Or, call it back to early mainframe days!)

Media often rate films, hotels and restaurants with "stars". Recently I worked on a project that rated how critical a problem was: the more stars in the rating, the more urgent it was that the problem be addressed. I wondered how hard it could be to use SQL Server to draw the sideways histograms that mainframes used to spit out with simple COBOL or FORTRAN code.

For starters, you can store the number of stars as a smallint, or even derive it using a calculation, as the statement below illustrates. Then comes the issue of presentation—how to turn a rating of 5 into five asterisks, as they might appear in a newspaper's film or restaurant review.

Now you must decide how you want to deliver the data. You have two choices:

- leave it up to the client. This is easy for you, but hard for the developers of each client because the more clients, the more redundant code.

- do it in the database. There's no downside to this option, and every client can simply extract the column without having to calculate.

I prefer using the backend rather than the frontend because for every additional frontend, new application-specific code is required. When you deliver the result from the backend, new applications can reuse the code.

A non database-dependent example

Assume a table with a column nameed NumberOfStars (varchar(5)), as well as all of the other required details. We can create a virtual column of type varchar that presents as many stars as the NumberOfStars column wants.

To begin, use this code (you may substitute any valid column reference for the second parameter):

SELECT REPLICATE( '*', 4)

Assuming a column called Stars is in a table called Reviews, you could write the following:

SELECT REPLICATE( '*', Reviews.Stars)
FROM Reviews

Try this command against the Northwind database:

SELECT Quantity, REPLICATE( '*', Quantity/5)
FROM [Order Details]
WHERE Discount IS NOT NULL

This will result in a list that begins like this:

12    **
10    **
5     *
9     *
40    ********
10    **
35    *******
15    ***
6     *
15    ***

In the code above, I use the most generic alphabet. Depending on the fonts available, you could make it more interesting by substituting a graphic character for the asterisk. For example, take a look through the three Wingdings fonts. If you see something you like, substitute that character for the asterisk. You won't see the results in Query Analyzer, which is typographically agnostic, but you should see them in another client.
Return to the Artful SQL Server & Access tips page