CODEWITHSUNDEEP
postgresql
Italo Rodrigo
Italo Rodrigo

Reputation: 1785

How to join two tables with nested field?

I have a table like this:

id | ciaps
 1 | a|b|c

An have a second table like:

cod | desc
  a | item a
  b | item b
  c | item c

I need a code to join this tables like:

id | ciaps
 1 | item a|item b|item c

Upvotes: 0

Views: 31

Answers (1)

Rahul Biswas
Rahul Biswas

Reputation: 3467

Use array_agg for concatenating string separated by '|' and convert it array_to_string to get the value expected format.

-- PostgreSQL (v11)
SELECT t1.id, t2.descr ciaps
FROM test1 t1
INNER JOIN (SELECT array_to_string(array_agg(cod), '|') cod
                 , array_to_string(array_agg(descr), '|') descr
            FROM test2) t2
        ON t1.ciaps = t2.cod;

Please check from url https://dbfiddle.uk/?rdbms=postgres_11&fiddle=6fffc7f1da6a02a48018b3691c99ad17

Upvotes: 1

Related Questions