A quick way to generate pretty charts using only SQL

Today I accidentally found I could generate simple charts in Squilbo using simple SQL code.


To get the orange lines, I've embedded some HTML in the SQL. This works because Squilbo runs in the web browser, and therefore if your SQL results contain HTML, the results grid will render it!

I'm not sure how useful all this is, but it is fun :)

I've blogged about generating SQL "Sparklines" a few times, and the general idea works in any SQL IDE (Query Analyzer, Toad etc).

The SQL looks like this:

select
year(date_created) as Year,
month(date_created) as Month, count(*) as Registrations,
concat(concat('<span style=''background: orange; color: white''>', replace(space(count(*)/10),' ','|')), '</span>') as Sparkline
from completions
group by 1,2
order by year(date_created) desc, month(date_created) desc
limit 20

It's the concat function that's the intersting bit.

See more advanced examples on my personal blog.

In case you're wondering, Squilbo isn't ready for public consumption yet. If you're interested in trying the BETA or just knowing when it's ready, then please let me know.