I have the below table:
create table test(
A1 varchar,
A2 varchar,
A3 varchar,
B1 varchar,
B2 varchar,
B3 varchar
);
insert into test values('How', 'Are', 'You', 'You', 'How', 'Are');
insert into test values('How', null , 'You', 'You', 'How', null);
I want to concatenate all A
strings and all B
strings, sort them and compare the 2.
This gives me the concatenation:
select (coalesce(A1, '') || ' ' || coalesce(A2,'') || ' ' || coalesce(A3, '')) as A,
(coalesce(B1, '') || ' ' || coalesce(B2,'') || ' ' || coalesce(B3, '')) as B
from test;
This is the output:
A B
How Are You You How Are
How You You How
As seen, both of these strings are same if we sort them and compare. How can I do this in Postgres?
Here is the fiddle to try:
http://sqlfiddle.com/#!15/b78281/6
CodePudding user response:
Would've been easier if you had an id
column, but row_number()
can be used. You can then union all
all the A* columns into one A column, same with B*. Then you can concatenate using string_agg
where you can choose how the words are to be sorted:
with u as
(select *, row_number() over(order by A1, A2, A3, B1, B2, B3) as rn
from test),
v as
(select A1 AS A, B1 as B, rn from u
union all select A2 AS A, B2 as B, rn from u
union all select A3 AS A, B3 as B, rn from u)
select string_agg(A, ' ' order by A) as A, string_agg(B, ' ' order by B) as B
from v
group by rn