CODEWITHSUNDEEP
sqlpostgresql
cdarwin
cdarwin

Reputation: 4291

Unifying tuples

I hope I remember SQL and that this is not a silly question...: I have the result of a complex query as a tuple containing two elements, I'd like to obtain a list containing a union of those elements. I'm using Postgresql but I'd like standard SQL

So, I have select foo which gives as a result 

(field_1_1, field_1_2),
(field_2_1, field_2_2),
...

and I want 

(field_1_1),
(field_1_2),
(field_2_1),
(field_2_2)

How can I do?

Upvotes: 1

Views: 51

Answers (2)

McNets
McNets

Reputation: 10807

I've used TRIM() function just to remove leading spaces from second element.

There isn't a standard SQL method for this job, each DMBS has specific functions.

create table tbl (foo text);
insert into tbl values
('field_1_1, field_1_2'),
('field_2_1, field_2_2');

✓

2 rows affected

select trim(from split_part(foo, ',', 1)) from tbl
union 
select trim(from split_part(foo, ',', 2)) from tbl;

| btrim     |
| :-------- |
| field_1_1 |
| field_1_2 |
| field_2_1 |
| field_2_2 |

select trim(from unnest(string_to_array(foo, ',')))
from   tbl;

| btrim     |
| :-------- |
| field_1_1 |
| field_1_2 |
| field_2_1 |
| field_2_2 |

dbfiddle here

Upvotes: 0

klin
klin

Reputation: 121604

You can use unnest():

with my_table (col1, col2) as (
values
    ('f11', 'f12'),
    ('f21', 'f22')
)

select unnest(array[col1, col2])
from my_table;

 unnest 
--------
 f11
 f12
 f21
 f22
(4 rows)

or union:

select col1
from my_table
union all
select col2
from my_table;

Note, that the order of resulting rows is undefined without the ORDER BY clause.

Upvotes: 1

Related Questions