Build Department Size Bar Chart Report with a Single SELECT statement

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s