CODEWITHSUNDEEP
sqlpostgresql
M.C
M.C

Reputation: 13

How to make a increment number column from one to a number in SQL

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?

Upvotes: 1

Views: 866

Answers (2)

user330315
user330315

Reputation:

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

Upvotes: 1

ta.speot.is
ta.speot.is

Reputation: 27214

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);

Upvotes: 0

Related Questions