Tricks and Tips: Creating a Character-Based Bar-Graph

If you ever need a quick bar chart generated in SQL using characters. Here's how to make it work.
This one assumes that the values you are passing in are percents.

   
   with
     w_data  as
       ( select  'Bob' as nm, 67 as pct  from dual union
         select  'Jan'      , 89         from dual union
         select  'Ray'      , 73         from dual union
         select  'Liz'      , 98         from dual union
         select  'Ron'      , 57         from dual union
         select  'Fay'      , 23         from dual       
       )
   select  nm
        ,  pct
        ,  rpad(rpad('|',1+( pct /100* 25 ),'>'), 25 +1,' ')||'|'   as "%Bar-25wide"
     from  w_data
    order  by  nm ;
   

And the Results:

   
   NM   PCT    %Bar-25wide
   ---  ----  ---------------------------
   Bob   67   |>>>>>>>>>>>>>>>>         |
   Fay   23   |>>>>>                    |
   Jan   89   |>>>>>>>>>>>>>>>>>>>>>>   |
   Liz   98   |>>>>>>>>>>>>>>>>>>>>>>>> |
   Ray   73   |>>>>>>>>>>>>>>>>>>       |
   Ron   57   |>>>>>>>>>>>>>>           |
   

The example above shows a simple set of six values (0 to 100) with a relatively simple one-liner in the SELECT clause that generates the graph. Notice that the "25" (two places) in the one-liner code determines the width of the character bar chart.

You can change the width -- and you can change the characters that are used in the graph. Take a look at the example below for some possibilities:

   
   with
     w_data  as
       ( select  'Bob' as nm, 67 as pct  from dual union
         select  'Jan'      , 89         from dual union
         select  'Ray'      , 73         from dual union
         select  'Liz'      , 98         from dual union
         select  'Ron'      , 81         from dual union
         select  'Fay'      , 23         from dual       
       )
   select  nm
        ,  pct
        ,  rpad(rpad('|',1+( pct /100* 10 ),'='), 10 +1,' ')||'|'   as "%Bar-10wide"
        ,  rpad(rpad('|',1+( pct /100* 20 ),'@'), 20 +1,'-')||'|'   as "%Bar-20wide"
        ,  rpad(rpad('|',1+( pct /100* 25 ),'>'), 25 +1,' ')||'|'   as "%Bar-25wide"
        ,  rpad(rpad('|',1+( pct /100* 50 ),'X'), 50 +1,'~')||'|'   as "%Bar-50wide"
     from  w_data
    order  by  nm ;
   

And the Results:

   
   Results:   
   
   NM   PCT   %Bar-10wide   %Bar-20wide             %Bar-25wide                  %Bar-50wide
   ---  ----  ------------  ----------------------  ---------------------------  ----------------------------------------------------
   Bob   67   |======    |  |@@@@@@@@@@@@@-------|  |>>>>>>>>>>>>>>>>         |  |XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX~~~~~~~~~~~~~~~~~|
   Fay   23   |==        |  |@@@@----------------|  |>>>>>                    |  |XXXXXXXXXXX~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
   Jan   89   |========  |  |@@@@@@@@@@@@@@@@@---|  |>>>>>>>>>>>>>>>>>>>>>>   |  |XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX~~~~~~|
   Liz   98   |========= |  |@@@@@@@@@@@@@@@@@@@-|  |>>>>>>>>>>>>>>>>>>>>>>>> |  |XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX~|
   Ray   73   |=======   |  |@@@@@@@@@@@@@@------|  |>>>>>>>>>>>>>>>>>>       |  |XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX~~~~~~~~~~~~~~|
   Ron   81   |========  |  |@@@@@@@@@@@@@@@@----|  |>>>>>>>>>>>>>>>>>>>>     |  |XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX~~~~~~~~~~|   
   

Enjoy!