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
John McKown
