On Tue, Feb 14, 2017 at 12:42 PM, John McKown <john.archie.mckown@xxxxxxxxx> wrote: > On Tue, Feb 14, 2017 at 12:04 PM, Alessandro Baggi > <alessandro.baggi@xxxxxxxxx> wrote: >> >> Hi list, >> sorry for my english, I will try to example as well. I've a query that >> joins multiple tables and return a result like: >> >> >> id,customers,phone,code,number >> 1 , aaaaaaaa,33333,123 , 2 >> 2 , aassdsds,33322,211 , 1 >> 3 , oooooooo,21221,221 , 1 >> >> >> I need, where "number" field is > 1, to duplicate the row * N(number field >> value) with a result like this: >> >> id,customers,phone,code,number >> 1 , aaaaaaaa,33333,123 , 2 >> 1 , aaaaaaaa,33333,123 , 2 >> 2 , aassdsds,33322,211 , 1 >> 3 , oooooooo,21221,221 , 1 >> >> How I can accomplish to this problem? >> >> I'm using postgresql 9.3.15 > > > I don't have PostgreSQL 9.3.15. I am running 9.5.5. But I think the > following recursive CTE should work for you. > > ==== > > tsh009=# \d baggi > Table "public.baggi" > Column | Type | Modifiers > -----------+---------+----------- > id | integer | > customers | text | > phone | text | > code | integer | > number | integer | > > tsh009=# select * from baggi; > id | customers | phone | code | number > ----+-----------+-------+------+-------- > 1 | aaaaaaaa | 33333 | 123 | 2 > 2 | aassdsds | 33322 | 211 | 1 > 3 | oooooooo | 21221 | 221 | 1 > (3 rows) > > tsh009=# with recursive multiple(k, id, customers, phone, code, number) as ( > select 1, id, customers, phone, code, number from baggi > UNION ALL > select m.k + 1, b.id, b.customers, b.phone, b.code, b.number from multiple > AS m, baggi AS b where m.id = b.id and m.k < b.number ) > select id, customers, phone, code, number from multiple order by id > ; > id | customers | phone | code | number > ----+-----------+-------+------+-------- > 1 | aaaaaaaa | 33333 | 123 | 2 > 1 | aaaaaaaa | 33333 | 123 | 2 > 2 | aassdsds | 33322 | 211 | 1 > 3 | oooooooo | 21221 | 221 | 1 > (4 rows) IMO, lateral join (available as of 9.3) is faster and simpler. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
