How to select data with multiple row output from 1 row (PostgreSQL)

Question

I have data on table test like this

customer_no | name | chance
---------------------------
00000000001 | AAAA |     3
00000000002 | BBBB |     2
00000000003 | CCCC |     1

Now,i want to select from table test with multiple output that calculate by value of field chance

output like this

customer_no | name
------------------
00000000001 | AAAA
00000000001 | AAAA
00000000001 | AAAA
00000000002 | BBBB
00000000002 | BBBB
00000000003 | CCCC

how to select command in pgsql database?

krokodilko · Accepted Answer · 2013-07-27 07:45:25Z

4

Try this:

SELECT  customer_no, name FROM (
  SELECT  test.*, 
          generate_series(1,chance) i
  FROM test
) test;

Here is a demo.

answered Jul 27, 2013 at 7:45

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

roman Over a year ago

+1, totally forgot about generate_series in postgres, i'm working with SQL Server all the time :)

roman · Accepted Answer · 2013-07-27 07:33:43Z

1

with recursive CTE_nums as (
    select max(chance) as num from test
    union all
    select num - 1 from CTE_nums
    where num > 1
)
select
   t.customer_no, t.name
from test as t
   inner join CTE_nums as n on n.num <= t.chance
order by 1, 2

SQL FIDDLE EXAMPLE

answered Jul 27, 2013 at 7:33

roman

118k30 gold badges205 silver badges209 bronze badges

Collectives™ on Stack Overflow

How to select data with multiple row output from 1 row (PostgreSQL)

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related