Background

In data-processing workflows it’s common to reshape tables: splitting values from multiple columns into multiple rows (unpivot), or aggregating rows back into columns (pivot). While Oracle offers PIVOT/UNPIVOT syntax, Db2 does not provide direct equivalents, but you can achieve the same results with standard SQL constructs.

This guide shows two practical patterns in Db2:

• Converting quarterly sales columns into rows
• Expanding a dependency graph and flattening multiple columns into a single column

Sample data: quarterly sales

-- Schema and table for examples
CREATE SCHEMA demo;

CREATE TABLE demo.fruit_sales (
  fruit_id    INT,
  fruit_name  VARCHAR(20),
  q1          INT,
  q2          INT,
  q3          INT,
  q4          INT
);

INSERT INTO demo.fruit_sales VALUES
  (1, 'Apple',  1000, 2000, 3300, 5000),
  (2, 'Orange', 3000, 3000, 3200, 1500),
  (3, 'Banana', 2500, 3500, 2200, 2500),
  (4, 'Grape',  1500, 2500, 1200, 3500);

SELECT * FROM demo.fruit_sales;

Unpivot (columns to rows)

Approach A: UNION ALL

A straightforward method is to UNION rows for each column:

SELECT fruit_id, fruit_name, 'Q1' AS quarter_label, q1 AS qty
FROM   demo.fruit_sales
UNION ALL
SELECT fruit_id, fruit_name, 'Q2', q2
FROM   demo.fruit_sales
UNION ALL
SELECT fruit_id, fruit_name, 'Q3', q3
FROM   demo.fruit_sales
UNION ALL
SELECT fruit_id, fruit_name, 'Q4', q4
FROM   demo.fruit_sales;

Approach B: CROSS JOIN LATERAL with VALUES

Db2’s TABLE(VALUES …) combined with a lateral join offers a compact unpivot:

SELECT f.fruit_id,
       f.fruit_name,
       v.quarter_label,
       v.qty
FROM demo.fruit_sales AS f
CROSS JOIN LATERAL (
  VALUES
    ('Q1', f.q1),
    ('Q2', f.q2),
    ('Q3', f.q3),
    ('Q4', f.q4)
) AS v(quarter_label, qty);

Notes:

• Each column within the VALUES constructor must have a consistent type across its rows (e.g., all labels are VARCHAR, all quantities are INT).
• CROSS JOIN LATERAL allows the VALUES clause to reference columns from the left table (f).

Dependency expansion example (row flattening)

Assume a table listing job dependencies where JOB_NM depends on PRE_JOB. Given a changed job, find all downstream jobs that must be rerun.

CREATE TABLE demo.job_seq (
  job_nm   VARCHAR(50),
  pre_job  VARCHAR(50),
  job_sts  VARCHAR(20)
);

INSERT INTO demo.job_seq (job_nm, pre_job) VALUES
  ('A1','A0'),
  ('A2','A1'),
  ('A3','A2'),
  ('A3','A0'),
  ('A4','A3'),
  ('A4','A0'),
  ('B0','A0'),
  ('B3','A3');

SELECT * FROM demo.job_seq;

Fixed-depth join, then flatten with VALUES

If you know the maximum depth to traverse, you can left-join multiple layers, then fold the resulting columns into a single column via VALUES:

-- Change the literal 'A1' to the job you’re analyzing
WITH chain AS (
  SELECT
    COALESCE(t0.job_nm, CAST(NULL AS VARCHAR(50))) AS job_nm0,
    COALESCE(t1.job_nm, CAST(NULL AS VARCHAR(50))) AS job_nm1,
    COALESCE(t2.job_nm, CAST(NULL AS VARCHAR(50))) AS job_nm2,
    COALESCE(t3.job_nm, CAST(NULL AS VARCHAR(50))) AS job_nm3
  FROM demo.job_seq AS t0                      -- layer 0
  LEFT JOIN demo.job_seq AS t1 ON t1.pre_job = t0.job_nm   -- layer 1
  LEFT JOIN demo.job_seq AS t2 ON t2.pre_job = t1.job_nm   -- layer 2
  LEFT JOIN demo.job_seq AS t3 ON t3.pre_job = t2.job_nm   -- layer 3
  WHERE t0.job_nm = 'A1'
)
SELECT DISTINCT x.job_nm
FROM chain AS c
CROSS JOIN LATERAL (
  VALUES (c.job_nm0), (c.job_nm1), (c.job_nm2), (c.job_nm3)
) AS x(job_nm)
WHERE x.job_nm IS NOT NULL;

General solution: recursive trvaersal

A recursive common table expression avoids hard-coding the depth and finds all downstream jobs:

-- Parameterize 'A1' as needed
WITH RECURSIVE deps(job_nm) AS (
  SELECT s.job_nm
  FROM   demo.job_seq AS s
  WHERE  s.pre_job = 'A1'
  UNION ALL
  SELECT s.job_nm
  FROM   demo.job_seq AS s
  JOIN   deps AS d
    ON   s.pre_job = d.job_nm
)
SELECT DISTINCT job_nm
FROM deps
ORDER BY job_nm;

You can drive updates directly from the recursive result:

-- Mark all dependent jobs as WAITING
UPDATE demo.job_seq
SET job_sts = 'WAITING'
WHERE job_nm IN (
  WITH RECURSIVE deps(job_nm) AS (
    SELECT s.job_nm FROM demo.job_seq AS s WHERE s.pre_job = 'A1'
    UNION ALL
    SELECT s.job_nm FROM demo.job_seq AS s JOIN deps AS d ON s.pre_job = d.job_nm
  )
  SELECT job_nm FROM deps
);

Pivot (rows to columns)

You can pivot using conditional aggrgeation with CASE expressions:

-- Start from unpivoted rows (quarter_label, qty), then pivot back to columns
WITH unpvt AS (
  SELECT f.fruit_id, f.fruit_name, v.quarter_label, v.qty
  FROM demo.fruit_sales AS f
  CROSS JOIN LATERAL (
    VALUES ('Q1', f.q1), ('Q2', f.q2), ('Q3', f.q3), ('Q4', f.q4)
  ) AS v(quarter_label, qty)
)
SELECT
  fruit_id,
  fruit_name,
  MAX(CASE WHEN quarter_label = 'Q1' THEN qty END) AS q1,
  MAX(CASE WHEN quarter_label = 'Q2' THEN qty END) AS q2,
  MAX(CASE WHEN quarter_label = 'Q3' THEN qty END) AS q3,
  MAX(CASE WHEN quarter_label = 'Q4' THEN qty END) AS q4
FROM unpvt
GROUP BY fruit_id, fruit_name;