Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();
ORA-00923: FROM keyword not found where expected

CREATE TABLE total_hours_played
(
  name VARCHAR (25) NOT NULL,
  hpt  SMALLINT NOT NULL
);
INSERT INTO total_hours_played VALUES ('George', 2);
1 rows affected

INSERT INTO total_hours_played VALUES ('Martina', 5);
1 rows affected

SELECT * FROM total_hours_played;


NAME
HPT


George
2

Martina
5


--
-- first attempt
--



WITH cte (name, x) 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 = name)
)
SELECT * FROM cte
ORDER BY name, x;


NAME
X


George
1

George
2

George
3

George
4

George
5

Martina
1

Martina
2

Martina
3

Martina
4

Martina
5


WITH  cte (name, x) 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;


NAME
X


George
1

George
2

Martina
1

Martina
2

Martina
3

Martina
4

Martina
5

NAME	X
George	1
George	2
George	3
George	4
George	5
Martina	1
Martina	2
Martina	3
Martina	4
Martina	5