Create n rows up to highest value in column for each unique client-CodePudding

I have this data in my postgresSQL db.

id	client_id	num
1	1	3
2	2	1
3	3	2

I want to create multiple entries for each unique client corresponding to the number in the num column. For example to complete this task for the above column I would have.

id	client_id	num
1	1	3
2	2	1
3	3	2
4	1	1
5	1	2
6	3	1

Filling in all numbers between 0 and num with new entries. I'd prefer to do this in a single command or loop versus the manually adding entries method that I know. Any help would be appreciated, thanks.

CodePudding user response：

You can do this by joining with generate_series using num as the max value. Use row_number to generate an ID for each row.

select
  row_number() over() as id,
  client_id,
  g.num as num
from clients
cross join generate_series(1, clients.num) as g(num)

Demonstration

CodePudding user response：

You can do it with a RECURSIVE query, that decrements your num index by one at each iteration, and stop when this value is equal to 1.

WITH RECURSIVE cte AS (
    SELECT id, client_id, num FROM tab
    UNION ALL
    SELECT id, client_id, num-1 
    FROM cte
    WHERE num-1 > 0
)
SELECT * FROM cte ORDER BY id, client_id, num

Check the demo here.