# SQL Tricks on Db2 z/OS – Part 1 Recursion and Table/Row pivoting

### by Tony Andrews (@Tony Andrews)

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.

## 1) SQL RECURSION

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

#### Example 1

Here is one way to use the recursion logic to get a list of Friday dates based on a starting point. Starting point must be a Friday date.
`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;` #### Example 2

Here is one that will show from the MIN(YEAR) of the number of emps hired by year. This was also executed against the Db2 EMP sample table.  A count with GROUP BY YEAR(HIREDATE) would work, but would only show the years that actually had employees hired in.
`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_CNTFROM CTE_HIREYR_RANGE RLEFT OUTER JOIN CTE_CNT_BY_HIREYR CON R.YR = C.YRORDER BY YR;` .......

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

#### EXAMPLE 1

Provide the number of Male managers and Female managers.

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.

#### Example 2

Show the number of employees for each different EDLEVEL as follows:
`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;` #### Example 3

Convert rows to columns with MAX. Get the MAX(HIREDATE) for the following jobs:

`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

Using the PROJACT table and this row to column pivoting technique, provide a report that counts the number of entries for each activity by month.  Provide columns for activities 60, 70 and 80 and a row for each  month of the ACSTDATE column. Notice no entries for any on the 3 activities for the month of November.
`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;` #### Example 5

In this example a GROUP BY on JOB, DEPTNO would have provided one row for each combination of JOB and DEPTNO with a count. If a tabular result is desired with one column’s values as the columns on the table and another for the rows, the result may be pivoted with the CASE statement.  CASE must be used to evaluate every desired department. This output shows by job, what department(s) contain an employee with that job title.

`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;` 782 views