CODEWITHSUNDEEP
sqlpostgresql
user3350179
user3350179

Reputation:

SQL : How to copy one row in a table in new table with multiple columns

I am using postgresql.
I have one table with only one row with columns like:

Name_1 | LastName_1 | Name_2 | LastName_2 | Name_3 | LastName_3
---------------------------------------------------------------
Tom    | Jones      | James  | Sunn       | Lee    | Harper

And I need to copy it in a new table with multiple rows with two columns like:

Name    | LastName
------------------
Tom     | Jones   
James   | Sunn     
Lee     | Harper

So that all from the first table Name_1, Name_2 and Name_3 go into new table under new column Name and same for LastName.

I found some options like PIVOT, but I don't know how to use it.

Upvotes: 1

Views: 52

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

If your data is not big, then the easiest way is union all:

select name_1 as name, lastname_1 as lastname from t union all
select name_2 as name, lastname_2 as lastname from t union all
select name_3 as name, lastname_3 as lastname from t;

There are other approaches for larger data or if performance is a major consideration.

Upvotes: 1

Related Questions