I am trying to form a transformation where I need to concatenate each 10 row value.
1st step: Table input (query from Postgres DB: select id from tablename)
sample result from the above query:
id
00000191-555c-11e4-922d-29fb57a42e4c
00000192-555c-11e4-922d-29fb57a42e4c
00000193-555c-11e4-922d-29fb57a42e4c
00000194-555c-11e4-922d-29fb57a42e4c
00000195-555c-11e4-922d-29fb57a42e4c
00000196-555c-11e4-922d-29fb57a42e4c
00000197-555c-11e4-922d-29fb57a42e4c
00000198-555c-11e4-922d-29fb57a42e4c
00000199-555c-11e4-922d-29fb57a42e4c
0000019a-555c-11e4-922d-29fb57a42e4c
000001a3-3cf2-11e4-b398-e52ee0ec6a4c
000002ad-3768-4242-88cf-96f27d0263af
000003ea-26e3-11e4-ace7-15c7d609fa6e
00000684-73fb-4d65-a502-87c4eb6607c1
0000087a-f587-44fa-8e88-7bcae5bcb22c
00000889-39c5-11e4-bd0e-c3f9d65ac856
0000094c-be98-4456-8b49-6357a36581aa
00000987-2f19-4574-ab85-6744a65ee4e3
00000cd0-4097-11e4-a4e6-af71a3d902c0
00000e1e-3b55-11e4-9897-d958d55e6784
here I have to concat each 10 rows ids into a single row. Eg. 1-10 row ids in a single row, 11-20 rows ids in another row and so on.
Expected Output:
ids
00000191-555c-11e4-922d-29fb57a42e4c,00000192-555c-11e4-922d-29fb57a42e4c,00000193-555c-11e4-922d-29fb57a42e4c,00000194-555c-11e4-922d-29fb57a42e4c,00000195-555c-11e4-922d-29fb57a42e4c,00000196-555c-11e4-922d-29fb57a42e4c,00000197-555c-11e4-922d-29fb57a42e4c,00000198-555c-11e4-922d-29fb57a42e4c,00000199-555c-11e4-922d-29fb57a42e4c,0000019a-555c-11e4-922d-29fb57a42e4c
000001a3-3cf2-11e4-b398-e52ee0ec6a4c,000002ad-3768-4242-88cf-96f27d0263af,000003ea-26e3-11e4-ace7-15c7d609fa6e,00000684-73fb-4d65-a502-87c4eb6607c1,0000087a-f587-44fa-8e88-7bcae5bcb22c,00000889-39c5-11e4-bd0e-c3f9d65ac856,0000094c-be98-4456-8b49-6357a36581aa,00000987-2f19-4574-ab85-6744a65ee4e3,00000cd0-4097-11e4-a4e6-af71a3d902c0,00000e1e-3b55-11e4-9897-d958d55e6784
I know Group By or Memory Group by will concat rows but in this scenario can I use it if so, how can I use it.
Please help me with this. Thanks in advance!
smth like this?
t=# \x
Expanded display is on.
t=# with a as
(
select ntile(2) over (order by id),id from tablename
)
select
string_agg(id,',')
from a
group by ntile;
-[ RECORD 1 ]-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
string_agg | 00000191-555c-11e4-922d-29fb57a42e4c, 00000192-555c-11e4-922d-29fb57a42e4c, 00000193-555c-11e4-922d-29fb57a42e4c, 00000194-555c-11e4-922d-29fb57a42e4c, 00000195-555c-11e4-922d-29fb57a42e4c, 00000196-555c-11e4-922d-29fb57a42e4c, 00000197-555c-11e4-922d-29fb57a42e4c, 00000198-555c-11e4-922d-29fb57a42e4c, 00000199-555c-11e4-922d-29fb57a42e4c, 0000019a-555c-11e4-922d-29fb57a42e4c
-[ RECORD 2 ]-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
string_agg | 000001a3-3cf2-11e4-b398-e52ee0ec6a4c, 000002ad-3768-4242-88cf-96f27d0263af, 000003ea-26e3-11e4-ace7-15c7d609fa6e, 00000684-73fb-4d65-a502-87c4eb6607c1, 0000087a-f587-44fa-8e88-7bcae5bcb22c, 00000889-39c5-11e4-bd0e-c3f9d65ac856, 0000094c-be98-4456-8b49-6357a36581aa, 00000987-2f19-4574-ab85-6744a65ee4e3, 00000cd0-4097-11e4-a4e6-af71a3d902c0, 00000e1e-3b55-11e4-9897-d958d55e6784