I am using this code to see the unique results of concated/joined columns 'a,b' from table1 and is working perfectly

WITH tt as ( 
SELECT concat(a,',',b) AS concated FROM table1 GROUP BY concated )
select concated  from tt;

But, instead of writing like 'a,b', I want to refer to table2.joins to get columns references to be concated/joined so that I can see together all the unique results of table1 (based on table2.joins)

Tried this but getting error. The expected result looks like this (demo);

WITH tt as ( 
SELECT concat(string_to_array((select joins from table2), ',')) AS concated FROM table1 GROUP BY concated )
select concated  from tt;

Any simple solution would be highly appreciated.

CodePudding user response：

Dynamic SQL can help to solve this problem, and here you need PL/pgSQL.

-- create table to store a result
create table if not exists concat_result (joins text);

do $$
declare
  -- instantiate all the necessary variables
  joins_from_table1 text[];
  joins_set text;
  current_sql text;
begin 

-- store all the rows as an array: {"a,b","a,c,f,i","a,d,o,x,y","a,e"}
joins_from_table1 := array(select joins from table2);

-- iterate over the array from the previous step
foreach joins_set in array joins_from_table1
loop
    -- concat_ws() helps to concat integers using a comma separator
    current_sql := 'insert into concat_result (joins) 
        select distinct concat_ws('','',' || joins_set || ') from table1;';
    -- simply log the current query (optional)
    raise info '%', current_sql;
    -- run sql and insert its result to the 'concat_result' table 
    execute current_sql;
end loop;

end $$;

CodePudding user response：

Is that what you are looking for :

WITH tt as (
  SELECT concat(unnest(string_to_array(joins, ','))) 
  AS concated 
  FROM tb_reference
  GROUP BY concated )
select concated  from tt;

joins is a column of tb_reference, so the FROM should be FROM tb_reference