Made to order: Eirik’s Hi-Lo Chart in SQL – extending the SQL Chart Palette (Pie, Stacked Bar Chart, Gauge, Gantt,…)

Share this on .. Tweet about this on TwitterShare on LinkedIn0Share on Facebook0Share on Google+0Email this to someoneShare on Tumblr0Buffer this page

With the series on SQL based Charts, I can now safely commit any crime in the firm knowledge that an insanity plea will undoubtedly be accepted. After spending way too much time on pretty nonsensical exercises like the Gauge and Stacked Bart Chart in SQL, I had decided not to do it again. But then, out of the blue, an urgent request reached me all the way from Norway: can you please do a Hi-Lo chart?

Never one to turn down anyone seeking help, I got into my SQL coding outfit and produced the following query, that produces graphically challenged results such as:

This type of chart is used to quickly give an impression of the range of a certain parameter – where are the extremes, what is the average – per category. In this example we see the Salary, per Department. The graphical layout makes it clear that Department 10 has the fat cats: it has the highest extreme as well as highest average (the asterisk) and is the only department to have an higher than average average.

We can use the same query....
with almost no modifications to plot the Salary per Job:

Or show the hiredate plotted as High-Low chart.

Note: there are about 10 lines of PL/SQL involved, all to create the markers (1000, 2200,…).The rest of it is pure SQL, not even Analytical Functions or other advanced, far fetched features.

The required PL/SQL is this piece:

create or replace
function string_table_to_string
( p_tbl in string_table_collection
) return varchar2
l_str varchar2(4000):='';
for i in 1..p_tbl.count loop
l_str:= l_str||p_tbl(i);
end loop;
return l_str;

Which requires the following nested table definition:

create or replace type string_table_collection
as table of varchar2(4000)

And then it’s all SQL from here on.

The query is largely driven from the chart-query in line view:

with chart_query as
( select deptno label
, max(sal) hi
, min(sal) lo
, sum(sal) summary
, count(sal) rec_count
, avg(sal) average
from emp
by deptno

It is this one that returns the data required by the query parts that actually draw the graph. Through the limits and chart_dimensions queries, we can do some layout settings like the width of the chart, the value range for the x-axis etc.:

, limits as
( select min(lo) overall_lo
, max(hi) overall_hi
, sum(summary)/sum(rec_count) overall_avg
, sum(rec_count) overall_sum
, max(length(label))+1 label_length
from chart_query
, chart_dimensions as
( select (overall_avg - x_min)/(x_max - x_min) * width axis_pos
, x_min
, x_max
, width
, marker_count
from ( select least(1000, 100*trunc(overall_lo/100)) x_min
, greatest(7000, 100*(1+trunc(overall_hi/100))) x_max
, 80 width
, overall_avg
, 6 marker_count
from limits



Download: hilo_chart.txt

Share this on .. Tweet about this on TwitterShare on LinkedIn0Share on Facebook0Share on Google+0Email this to someoneShare on Tumblr0Buffer this page

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press books: Oracle SOA Suite 11g Handbook and Oracle SOA Suite 12c Handbook. Frequent presenter on community events and conferences such as JavaOne, Oracle Code and Oracle OpenWorld.


  1. Anton Scheffer on

    Why use a separate function (string_table_to_string) and type (string_table_collection)? You can use something like the following SQL to do the same.


    ( select markers
    from ( select marker markers
    , rn
    from marker_values
    , chart_dimensions
    dimension by (row_number() over (order by marker_value desc) rn)
    measures (rpad(marker_value, width /(marker_count-1) ) marker)
    rules ( marker[any] order by rn desc = marker[cv()+1] || marker[cv()] )
    where rn = 1