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