I have a table similar to this one:

From which I want to generate a table such as this one:

Each entry in the first table translates to a series in the second that goes up until the number in 'total_hours_played' and is grouped by 'name'. How can I do this in PostgreSQL?

Thank you in advance.

CodePudding user response：

In order to solve this, I did the following (all the code below is available on the fiddle here):

CREATE TABLE total_hours_played
(
  name TEXT NOT NULL,
  hpt  SMALLINT NOT NULL
);

populate it:

INSERT INTO total_hours_played VALUES
('George', 2),
('Martina', 5);

Then run this SQL:

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;

Result:

name        x
George      1
George      2
Martina     1
Martina     2
Martina     3
Martina     4
Martina     5

Et voilà!

CodePudding user response：

You can generate_series on each row of input for that row. (see demo here)

with test (name, hours_played)  as
     ( values ('George',2)
            , ('Martina', 5)
     ) 
 -- your query starts here
select name 
     , generate_series(1, hours_played)  
 from test;