Re: postgresql how to duplicate rows in result.

On Tue, Feb 14, 2017 at 12:04 PM, Alessandro Baggi <
alessandro(dot)baggi(at)gmail(dot)com> 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)

====

> .
>
> thanks in advance.
>
> Alessandro.
>

--
"Irrigation of the land with sewater desalinated by fusion power is
ancient. It's called 'rain'." -- Michael McClary, in alt.fusion

Maranatha! <><
John McKown