Using CTE with multiple criteria

Question

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)

line	sets	joins
1	2	a,b
2	4	a,c,f,i
3	5	a,d,o,x,y
4	2	a,e

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.

Answer 1

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

drop table if exists concat_result;
-- create table to store a result
create table if not exists concat_result (column_header text, 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
    -- if you need unique values use DISTINCT after SELECT
    current_sql := 'insert into concat_result (column_header, joins) 
        select ''' || joins_set || ''', 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 $$;