CODEWITHSUNDEEP
postgresql
stefan
stefan

Reputation: 1

Postgresql unnest record in different way

I have record with something like that 

1|{4947583,4947582,4947581,4947580}
2|{4947590,4947589,4947588}
3|{4947613,4947612}

type integer[]

and I want to have id in this order

4947583,4947590,4947613,4947582,4947589,4947612,4947581,4947588,4947580

Upvotes: 0

Views: 3092

Answers (2)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95512

Assuming that 1, 2, and 3 are part of the row . . .

create table test (
  test_id serial primary key,
  ints integer[]
);

insert into test (ints) values ('{4947583,4947582,4947581,4947580}');
insert into test (ints) values ('{4947590,4947589,4947588}');
insert into test (ints) values ('{4947613,4947612}');

select test_id, unnest, row_number() over (partition by test_id order by unnest desc) 
from (select test_id, unnest(ints) from test) x 
order by row_number, test_id;

 test_id | unnest  | row_number
---------+---------+------------
       1 | 4947583 |          1
       2 | 4947590 |          1
       3 | 4947613 |          1
       1 | 4947582 |          2
       2 | 4947589 |          2
       3 | 4947612 |          2
       1 | 4947581 |          3
       2 | 4947588 |          3
       1 | 4947580 |          4

The row_number() function has the effect of generating a 1-based index for each array.

Upvotes: 0

Kirk Roybal
Kirk Roybal

Reputation: 17837

WITH myvalues AS (
SELECT id, unnest(myarray) myvals
FROM (
VALUES (1,string_to_array('4947583,4947582,4947581,4947580', ',')),
    (2, string_to_array('4947590,4947589,4947588', ',')),
    (3, string_to_array('4947613,4947612', ',')) ) v (id, myarray)
    ),
iterated AS (SELECT *, row_number() OVER (PARTITION BY id)
FROM myvalues)
SELECT array_to_string(array( SELECT myvals
FROM iterated i1
ORDER BY row_number, id), ',')

This could probably be cooked down a little bit for clarity, but it works.

Upvotes: 1

Related Questions