I have a table as:
table1(id int, count int)
Now I want to get a result that contains table1's id and a increment number column from one to count. For example,table1 has two rows of data:
id count 1 3 2 4
then result should be
id nr
1 1
1 2
1 3
2 1
2 2
2 3
2 4
How can I do it with PostgreSQL or SQL Sever?
In Postgres you can use generate_series()
select t1.id, g.nr
from table1 t1
cross join lateral generate_series(1, t1.count) as g(nr)
order by t1.id, g.nr;
The recursive CTE also works in Postgres:
WITH recursive cte as (
SELECT id, count, 1 as nr
FROM table1
UNION ALL
SELECT id, count, nr + 1
from cte
WHERE nr < count
)
SELECT id, nr
FROM cte
ORDER BY id, nr;
Online example: http://rextester.com/KNQG24769
How can I do it with postgresql or SQL Sever
You can do this in SQL Server with a recursive CTE.
WITH cteNumbers
AS (SELECT Id,
1 [Sequence],
[Count]
FROM Table1
UNION ALL
SELECT Id,
[Sequence] + 1,
[Count]
FROM cteNumbers
WHERE [Sequence] < [Count])
SELECT Id,
[Sequence]
FROM cteNumbers
ORDER BY 1,
2
OPTION (MAXRECURSION 0);
