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

By John Maenpaa posted 25 days ago

  

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_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):

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;
2 comments
782 views

Permalink

Comments

11 days ago

We play a lot with this kind of process. 

The problem to make a conversion row to column from my point of view is that you must know all the different  value before implementing your case

19 days ago

I have been playing with pivot tables in Excel. this is great to note have to go through those steps, to export, convert.