Question 90145 in PostgreSQL Question posted in
The official documentation can be found here.

Using CTE with multiple criteria – Postgresql

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.

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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 $$;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search