Lpad Function For Data Visualization- Making Bar Chart

Problem

Lpad() is a very cool function that can be used for data visualization. It takes three parameters as an example shown below. The first parameter is the string to display('hello'), the second one is the length of the resulting string (10), the third one if the character(*) that will be used to fill the spaces on the left.

SQL> select lpad('hello',10,'*') s from dual;

S
----------
*****hello

We can use lpad to display "bar chart" based on the data. For example, we have the following daily stock price data. How do we display the price variation visually?

SQL> select * from stock;

SYM DAT            PRICE
--- --------- ----------
XYZ 31-MAY-11         14
XYZ 01-JUN-11         19
XYZ 02-JUN-11         21
XYZ 03-JUN-11         23
XYZ 04-JUN-11         27
XYZ 05-JUN-11         14
XYZ 06-JUN-11         17
XYZ 07-JUN-11         22
XYZ 08-JUN-11         26
XYZ 09-JUN-11         27
XYZ 10-JUN-11         21
XYZ 11-JUN-11         17
XYZ 12-JUN-11         27
XYZ 13-JUN-11         27
XYZ 14-JUN-11         16
XYZ 15-JUN-11         14
XYZ 16-JUN-11         16
XYZ 17-JUN-11         26
XYZ 18-JUN-11         25
XYZ 19-JUN-11         24

20 rows selected.

Solution

We use the following query. Lpad('*', price, '*') means it will make a string "*" of the length of "price" with left spaces filled with '*'.

SQL> select dat, price, lpad('*', price, '*') price_bar 
from stock;

DAT            PRICE PRICE_BAR
--------- ---------- ----------------------------------------
31-MAY-11         14 **************
01-JUN-11         19 *******************
02-JUN-11         21 *********************
03-JUN-11         23 ***********************
04-JUN-11         27 ***************************
05-JUN-11         14 **************
06-JUN-11         17 *****************
07-JUN-11         22 **********************
08-JUN-11         26 **************************
09-JUN-11         27 ***************************
10-JUN-11         21 *********************
11-JUN-11         17 *****************
12-JUN-11         27 ***************************
13-JUN-11         27 ***************************
14-JUN-11         16 ****************
15-JUN-11         14 **************
16-JUN-11         16 ****************
17-JUN-11         26 **************************
18-JUN-11         25 *************************
19-JUN-11         24 ************************

20 rows selected.