Search Postgresql Archives

Re: postgresql how to duplicate rows in result.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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)



====

 
.

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux