Table1

I want another column that has values from the list

list1 = [Zoo, Home]

Expected Output

CodePudding user response：

First get the database normalized, and then use string_agg() for the result:

select Animal,string_agg(Habitat,',') as Habitat
from (
   select animal, trim(unnest( string_to_array(Habitat,','))) as Habitat 
   from table1
) x
where Habitat in (select trim(unnest( string_to_array('Zoo,Home',','))))
group by Animal;

see: DBFIDDLE

CodePudding user response：

Assuming table1 is defined like this:

create table table1 as 
select animal, habitat from (values 
 ('Lion',Array ['Zoo','Forest','Home','Sea']),
 ('Fish',Array['Sea', 'Zoo', 'Aquarium']),
 ('Dog',Array['Home', 'Zoo', 'Wild']),
 ('Bird',Array['Sea', 'Forest', 'Home'])) as a(animal, habitat)

intersection of arrays can be done like this

select animal,array_agg(one_habitat) from (
  select animal, unnest(habitat) one_habitat from table1
) a 
where one_habitat = ANY(Array['Zoo','Home'])
group  by animal

Assuming, that habitat column is not real array

create table table2 as 
select animal, habitat from (values 
 ('Lion','Zoo, Forest, Home, Sea'),
 ('Fish','Sea, Zoo, Aquarium'),
 ('Dog','Home, Zoo, Wild'),
 ('Bird','Sea, Forest, Home')) as a(animal, habitat)

Query

select animal,string_agg(one_habitat,', ') from (
  select animal, unnest(string_to_array(habitat,', ')) one_habitat from table2
) a 
where one_habitat = ANY(Array['Zoo','Home'])
group  by animal

DB Fiddle