SQL tricks is a rather broad term, and these articles are really about a number of unique / different things that can be done in the SQL programming language that hopefully readers can learn from and possibly use. There are so many in our Db2 industry that are very skilled in the SQL language and many of the examples provided in all of these articles came from them. This article focuses specifically on SQL recursion and pivoting of data from rows to columns.

Developers have probably used this recursion especially with hierarchical or bill of material tables to dig down within a hierarchy like employee charts, department charts, and multi-level bill of material manufacturing products.

Note: Level number is always a good idea in every recursion query to make sure you do not code something wrong and go into an infinite loop. Without the level number and logic to stop after ‘so many’ levels, the query would then a SQLCODE Warning of +347 THE RECURSIVE COMMON TABLE EXPRESSION’ CTE-NAME’ MAY CONTAIN AN INFINITE LOOP

WITH TAB1 (FRIDAY_DT,LVL) AS

( SELECT DATE('03/27/2020') AS FRIDAY_DT, 1 AS LVL

FROM SYSIBM.SYSDUMMY1

UNION ALL

SELECT DATE(X.FRIDAY_DT) - 7 DAYS, X.LVL + 1

FROM TAB1 X

WHERE DATE(X.FRIDAY_DT) - 7 DAYS > DATE('12/31/2019')

AND X.LVL < 100

)

SELECT * FROM TAB1;

WITH CTE_HIREYR_RANGE (YR, LVL) AS

( SELECT MIN(YEAR(HIREDATE)), 1 FROM EMP

UNION ALL

SELECT YR+1, LVL+1 FROM CTE_HIREYR_RANGE

WHERE YR < (SELECT MAX(YEAR(HIREDATE)) FROM EMP)

AND LVL < 999

)

, CTE_CNT_BY_HIREYR AS

( SELECT YEAR(HIREDATE) AS YR, COUNT(*) AS HIRE_CNT

FROM EMP E

GROUP BY YEAR(HIREDATE)

)

SELECT R.YR AS YR, COALESCE(HIRE_CNT,0) AS HIRE_CNT

FROM CTE_HIREYR_RANGE R

LEFT OUTER JOIN CTE_CNT_BY_HIREYR C

ON R.YR = C.YR

ORDER BY YR;

2) SQL to convert rows into columns (pivoting):

If you use the GROUP BY:

SELECT SEX, COUNT(*) as Number_Of_Managers

FROM EMP

WHERE JOB = ‘MANAGER’

GROUP BY SEX;

If you use the SUM/CASE:

SELECT SUM(CASE WHEN SEX = ‘F’ THEN 1 ELSE 0 END) as Number_Of_Female_Managers,

SUM(CASE WHEN SEX = ‘M’ THEN 1 ELSE 0 END) as Number_Of_Male_Managers

FROM EMP;

The CASE expression may also be used in conjunction with column functions to change a result from a vertical to horizontal display.

SELECT SUM(CASE WHEN EDLEVEL < 12 THEN 1 ELSE 0 END) AS EDLEVEL_LT12,

SUM(CASE WHEN EDLEVEL = 12 THEN 1 ELSE 0 END) AS EDLEVEL_12,

SUM(CASE WHEN EDLEVEL = 13 THEN 1 ELSE 0 END) AS EDLEVEL_13,

SUM(CASE WHEN EDLEVEL = 14 THEN 1 ELSE 0 END) AS EDLEVEL_14,

SUM(CASE WHEN EDLEVEL = 15 THEN 1 ELSE 0 END) AS EDLEVEL_15,

SUM(CASE WHEN EDLEVEL = 16 THEN 1 ELSE 0 END) AS EDLEVEL_16,

SUM(CASE WHEN EDLEVEL = 17 THEN 1 ELSE 0 END) AS EDLEVEL_17,

SUM(CASE WHEN EDLEVEL = 18 THEN 1 ELSE 0 END) AS EDLEVEL_18,

SUM(CASE WHEN EDLEVEL > 18 THEN 1 ELSE 0 END) AS EDLEVEL_GT18

FROM EMP;

SELECT MAX(CASE WHEN JOB = 'ANALYST' THEN HIREDATE END) AS ANALYST_MAX_HIREDATE,

MAX(CASE WHEN JOB = 'CLERK' THEN HIREDATE END) AS CLERK_MAX_HIREDATE,

MAX(CASE WHEN JOB = 'DESIGNER' THEN HIREDATE END) AS DESIGNER_MAX_HIREDATE,

MAX(CASE WHEN JOB = 'MANAGER' THEN HIREDATE END) AS MANAGER_MAX_HIREDATE

FROM EMP

WHERE JOB IN ('ANALYST', 'CLERK', 'DESIGNER', 'MANAGER');

Example 4

SELECT MONTH(ACSTDATE) AS MONTH,

SUM(CASE WHEN ACTNO = 60 THEN 1 ELSE 0 END ) AS ACT60,

SUM(CASE WHEN ACTNO = 70 THEN 1 ELSE 0 END ) AS ACT70,

SUM(CASE WHEN ACTNO = 80 THEN 1 ELSE 0 END ) AS ACT80

FROM PROJACT

WHERE ACTNO IN (60,70,80)GROUP BY MONTH(ACSTDATE)ORDER BY MONTH;

SELECT JOB,

SUM(CASE WHEN DEPTNO = 'A00' THEN 1 ELSE 0 END) AS A00,

SUM(CASE WHEN DEPTNO = 'B01' THEN 1 ELSE 0 END) AS B01,

SUM(CASE WHEN DEPTNO = 'C01' THEN 1 ELSE 0 END) AS C01,

SUM(CASE WHEN DEPTNO = 'D11' THEN 1 ELSE 0 END) AS D11,

SUM(CASE WHEN DEPTNO = 'D21' THEN 1 ELSE 0 END) AS D21,

SUM(CASE WHEN DEPTNO = 'E01' THEN 1 ELSE 0 END) AS E01,

SUM(CASE WHEN DEPTNO = 'E11' THEN 1 ELSE 0 END) AS E11,

SUM(CASE WHEN DEPTNO = 'E21' THEN 1 ELSE 0 END) AS E21

FROM EMP

GROUP BY JOB;

3 comments

829 views

That's true but simply add a catch all spot at the end.

Example, 5.

SELECT JOB,

SUM(CASE WHEN WORKDEPT = 'A00' THEN 1 ELSE 0 END) AS A00,

SUM(CASE WHEN WORKDEPT = 'B01' THEN 1 ELSE 0 END) AS B01,

SUM(CASE WHEN WORKDEPT = 'C01' THEN 1 ELSE 0 END) AS C01,

SUM(CASE WHEN WORKDEPT = 'D11' THEN 1 ELSE 0 END) AS D11,

SUM(CASE WHEN WORKDEPT = 'D21' THEN 1 ELSE 0 END) AS D21,

SUM(CASE WHEN WORKDEPT = 'E01' THEN 1 ELSE 0 END) AS E01,

SUM(CASE WHEN WORKDEPT = 'E11' THEN 1 ELSE 0 END) AS E11,

-- SUM(CASE WHEN WORKDEPT = 'E21' THEN 1 ELSE 0 END) AS E21, -- commented out to show data in unknown area

SUM(CASE WHEN WORKDEPT NOT IN ('A00','B01','C01','D11','D21','E01','E11'

--,'E21' -- commented out to show data in unknown area

) THEN 1 ELSE 0 END) AS WORKDEPT_NOT_KNOWN

FROM DSN8910.EMP --EMP

GROUP BY JOB;