I came across this problem recently (the simple table DDL is available in the links below):
I have a table:
and the question was how to generate this:
Now, I did it this way:
See the link here (Oracle) and here (PostgreSQL).
WITH RECURSIVE cte AS
SELECT name, 1 AS x FROM total_hours_played
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;
As you can see,they are identical.
However, there's a very elegant PostgreSQL solution (link here) as follows:
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.