Re: postgresql how to duplicate rows in result.

On Tue, Feb 14, 2017 at 12:42 PM, John McKown
<john(dot)archie(dot)mckown(at)gmail(dot)com> wrote:
> 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)

IMO, lateral join (available as of 9.3) is faster and simpler.

merlin