CODEWITHSUNDEEP
sqlpostgresql
weeraa
weeraa

Reputation: 1195

Combined 2 columns into one column SQL

I'm a beginner to Postgres and need to do something like this. My Postgres query output has 2 columns. I need to combine these two like below.

Column 1:

A
B
C

Column 2:

D
D
D

Output Column:

A
B
C
D

(all values from column 1 and distinct values from column 2)

Is this possible in PostgreSQL?

Upvotes: 8

Views: 10036

Answers (4)

Dimo Boyadzhiev
Dimo Boyadzhiev

Reputation: 1357

Hi there is one fancy way:

select distinct unnest(array[
        column1
        , column2
    ])
from table

Upvotes: 15

user3206010
user3206010

Reputation: 121

I had the same problem. I have 3 colums and want to combine the rows into one and want no duplicated values. There for I merged them in the first step via col1 || '#' || col2 || '#' || col3. Then I used the string_to_array function to split the string into array to use unnest in the last step. After all I can use DISTINCT over my result.

SELECT DISTINCT unnest(string_to_array(col1 || '#' || col2 || '#' || col3, '#')) FROM my_table;

Upvotes: 0

user330315
user330315

Reputation:

Put your original query into a common table expression then aggregate the distinct values of each column:

with data (fromzoneid, fromzoneid2) as ( 
  ... your complete original query goes here ...
)
select string_agg(distinct c1, '') as zoneid
from (
  select t1.c1
  from data
    cross join regexp_split_to_table(fromzoneid, '') as t1 (c1)
  union 
  select t2.c1
  from data
    cross join regexp_split_to_table(fromzoneid2, '') as t2 (c1)
)  t;

Attention

The above assumes that your query only returns a single row. My solution will aggregate the values for all rows that are returned by your query! If you query returns more than row, the unique characters of all rows are combined into a single column and single row!

If you don't want that, you need an additional column in your query that can be used for a group by statement

Something like: 

with data (id, fromzoneid, fromzoneid2) as ( 
  ... your complete original query goes here ...
)
select id, string_agg(distinct c1, '') as zoneid
from (
  select data.id, t1.c1
  from data
    cross join regexp_split_to_table(fromzoneid, '') as t1 (c1)
  union 
  select data.id, t2.c1
  from data
    cross join regexp_split_to_table(fromzoneid2, '') as t2 (c1)
)  t
group by id

Upvotes: 0

elirevach
elirevach

Reputation: 404

You need something like this : 

 select  col    from (
   select Column1 as col   from <your table >
    union all 
    select distinct Column2 as col   from <your table>
) as myview order by col

Upvotes: 1

Related Questions