Postgres Pro
Downloads
Home   >   mailing lists

Re: [GENERAL] postgresql how to duplicate rows in result. - Mailing list pgsql-general

From John McKown
Subject Re: [GENERAL] postgresql how to duplicate rows in result.
Date
Msg-id CAAJSdjg78oBVmPdRLA_VLnFSmn01Fy4HR1415RvdLMQneLxAaQ@mail.gmail.com
Whole thread Raw
In response to [GENERAL] postgresql how to duplicate rows in result.  (Alessandro Baggi <alessandro.baggi@gmail.com>)
Responses Re: [GENERAL] postgresql how to duplicate rows in result.  (Merlin Moncure <mmoncure@gmail.com>)
List pgsql-general
Tree view
On Tue, Feb 14, 2017 at 12:04 PM, Alessandro Baggi <alessandro.baggi@gmail.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

pgsql-general by date:

Previous
From: Merlin Moncure
Date:
Subject: Re: [GENERAL] postgresql how to duplicate rows in result.
Next
From: Shawn Thomas
Date:
Subject: [GENERAL] Can't restart Postgres