Is there a way to turn a single row from a DB2 SQL statement result set into multiple rows based on-CodePudding

If I have a SQL query like this:

SELECT A.W, A.X, B.Y, B.Z 
FROM A 
INNER JOIN B ON A.value1 = B.value2;

Which is returning a row like this:

 W | X | Y | Z
--- --- --- --
 w | x | y | z

Is there a way to modify that statement so the result will be something like this table

 W | X | Y1  | Y2  | Z
--- --- ----- ----- ---
 w | x | y 0 | y 1 | z
 w | x | y 2 | y 3 | z
 w | x | y 4 | y 5 | z
 w | x | y 6 | y 7 | z
 w | x | y 8 | y 9 | z

where the Y1 and Y2 columns are derived from the original y value

CodePudding user response：

Use a CTE that returns the integers 0, 2, 4, 6 and 8 and cross join it to your query:

WITH cte(n) AS (VALUES (0), (2), (4), (6), (8))
SELECT A.W, A.X, 
       B.Y   c.n AS Y1,
       B.Y   c.n   1 AS Y2, 
       B.Z 
FROM A 
LEFT JOIN B ON A.value1 = B.value2
CROSS JOIN  cte AS c;

If you want a dynamic set of integers use a recursive CTE:

WITH cte(n) AS (
  SELECT 0 FROM SYSIBM.SYSDUMMY1    
  UNION ALL
  SELECT n   2 
  FROM cte 
  WHERE n < 8 -- change 8 to the number that you want
)
SELECT A.W, A.X, 
       B.Y   c.n AS Y1,
       B.Y   c.n   1 AS Y2, 
       B.Z 
FROM A 
LEFT JOIN B ON A.value1 = B.value2
CROSS JOIN  cte AS c;