## Build Department Size Bar Chart Report with a Single SELECT statement.

Recently, when I was working on a completely different problem, I realized that in some cases, SQL plus will allow us generating simple bar graphs on selected data. I challenged myself to build a “graph” that will look like this:

10 20 30 -- -- -- X X X X X X X X X X X X X X

Each column represents a “bar” and its “height” represents the number of employees working in a corresponding department.

Below, you will find several strategies for building such “graphs” as well as generating “reverse graphs”:

N 10 20 30 --- -- -- -- 6 X 5 X X 4 X X 3 X X X 2 X X X 1 X X X

**Method/Workaround 1: Pivot simulation**

WITH x AS (SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno ), y AS ( SELECT LEVEL n, 'X' as c FROM dual CONNECT BY LEVEL<=(SELECT MAX(cnt) FROM x) ) SELECT MAX(CASE WHEN x.deptno=10 THEN y.c END) "10", MAX(CASE WHEN x.deptno=20 THEN y.c END) "20", MAX(CASE WHEN x.deptno=30 THEN y.c END) "30" FROM y JOIN x ON y.n<=x.cnt GROUP BY y.n ORDER BY y.n / 10 20 30 -- -- -- X X X X X X X X X X X X X X

**Method/Workaround 2: Pivot**

SELECT "10","20","30" FROM ( WITH x AS ( SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno ) SELECT LEVEL n, deptno, 'X' as c FROM dual, x WHERE LEVEL<=x.cnt CONNECT BY LEVEL<=(SELECT MAX(cnt) FROM x) ) PIVOT ( MAX(c) FOR deptno IN (10 "10",20 "20",30 "30") ) ORDER BY N /

**Method/Workaround 3: Leverage Department/Employee Roll Puzzle:**

SELECT "10","20","30" FROM ( SELECT ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY ename) rn, deptno, 'X' c FROM emp ) PIVOT ( MAX(c) FOR deptno IN (10,20,30) ) ORDER BY rn; 10 20 30 -- -- -- X X X X X X X X X X X X X X

**Method/Workaround 4:**

WITH x AS ( SELECT CASE WHEN deptno=10 THEN 'X' END "10", CASE WHEN deptno=20 THEN 'X' END "20", CASE WHEN deptno=30 THEN 'X' END "30", ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY ename) rn FROM emp ) SELECT MAX("10") AS "10", MAX("20") AS "20", MAX("30") AS "30" FROM x GROUP BY rn ORDER BY rn;

**Reverse Bar Chart Report:**

**Method/Workaround 1:**

WITH x AS (SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno ), y AS ( SELECT LEVEL n, 'X' as c FROM dual CONNECT BY LEVEL<=(SELECT MAX(cnt) FROM x) ) SELECT n, MAX(CASE WHEN x.deptno=10 THEN y.c END) "10", MAX(CASE WHEN x.deptno=20 THEN y.c END) "20", MAX(CASE WHEN x.deptno=30 THEN y.c END) "30" FROM y JOIN x ON y.n<=x.cnt GROUP BY y.n ORDER BY y.n DESC; N 10 20 30 --- -- -- -- 6 X 5 X X 4 X X 3 X X X 2 X X X 1 X X X

**Method/Workaround 2:**

SELECT * FROM ( WITH x AS ( SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno ) SELECT LEVEL n, deptno, 'X' as c FROM dual, x WHERE LEVEL<=x.cnt CONNECT BY LEVEL<=(SELECT MAX(cnt) FROM x) ) PIVOT ( MAX(c) FOR deptno IN (10 "10",20 "20",30 "30") ) ORDER BY N DESC;

**Method/Workaround 3: Leverage Department/Employee Roll Puzzle:**

SELECT * FROM ( SELECT ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY ename) n, deptno, 'X' c FROM emp ) PIVOT ( MAX(c) FOR deptno IN (10,20,30) ) ORDER BY n DESC;

**Method/Workaround 4:**

WITH x AS ( SELECT CASE WHEN deptno=10 THEN 'X' END "10", CASE WHEN deptno=20 THEN 'X' END "20", CASE WHEN deptno=30 THEN 'X' END "30", ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY ename) n FROM emp ) SELECT n, MAX("10") AS "10", MAX("20") AS "20", MAX("30") AS "30" FROM x GROUP BY n ORDER BY n DESC;

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book **“Oracle SQL Tricks and Workarounds”** for instructions.