CODEWITHSUNDEEP
sqlpostgresql
Luckatme
Luckatme

Reputation: 9

PGSQL Help, SELECT Row Binding

i want to ask about postgresql, i couldnt find my answer from google. Okay, lets get started,

suppose i have a table named 'name' with 20 rows, which has the column 'first_name' and 'last_name'.

what SQL i should use to make the query return only one column that contain all the value in 'first_name' and 'last_name' so that it return 40 rows (20 from 'first_name' and another 20 from 'last_name')?

Thanks for your help,

Upvotes: 0

Views: 186

Answers (2)

markusk
markusk

Reputation: 6667

Another way to transpose columns to rows:

SELECT UNNEST(ARRAY[first_name, last_name]) AS name
FROM name;

This approach will do a single sequential scan on the table, while the UNION ALL approach will do two scans. Which approach performs better is likely to depend on your data.

Upvotes: 0

WJS
WJS

Reputation: 714

You can do this using the UNION operator and aliasing the columns to the same name, as below:

SELECT 
first_name AS names
FROM name
UNION
SELECT 
last_name AS names
FROM name;

However the UNION operator will remove duplicate name, to include everything, including duplicate names use the UNION ALL operator as below:

SELECT 
first_name AS names
FROM name
UNION ALL
SELECT 
last_name AS names
FROM name;

Upvotes: 2

Related Questions