Hi,
I came across this problem recently (the simple table DDL is available in the links below):
I have a table:
NAME X
George 2
Martina 5
and the question was how to generate this:
NAME X
George 1
George 2
Martina 1
Martina 2
Martina 3
Martina 4
Martina 5
Now, I did it this way:
WITH RECURSIVE cte AS
(
SELECT name, 1 AS x FROM total_hours_played
UNION ALL
SELECT name, x + 1 FROM cte
WHERE x <
(SELECT MAX(hpt) FROM total_hours_played
WHERE name = (SELECT name FROM total_hours_played WHERE name = cte.name))
)
SELECT * FROM cte
ORDER BY name, x;
See the link here (Oracle) and here (PostgreSQL).
As you can see,they are identical.
However, there's a very elegant PostgreSQL solution (link here) as follows:
SELECT
name,
GENERATE_SERIES(1, x)
FROM
total_hours_played;
My question is, is there any way of doing something similar to the PostgreSQL solution with Oracle?
Any other possible solutions also appreciated.
TIA and rgs.