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.
sqlpatterns 