How to split several columns into one column with several records in SQL?-CodePudding

I need to transform the data using SQL but I'm struggling with one thing currently.

I have a table Person with these columns:

phone_number; name_1; name_2; name3; name4.

I need to transform this table to table like this:

phone_number; name

Where would be such records as

(phone_number; name_1)
(phone_number; name_2)
(phone_number; name_3)

Please help me (example is below)

CREATE TABLE Person 
(
    phone_number int,
    name_1 varchar(50),
    name_2 varchar(50),
    name_3 varchar(50)
);

INSERT INTO Person (phone_number, name_1, name_2, name_3)
VALUES (123, 'Billy', 'Bill', 'Bi')
    
/* Expected: */

/*
phone_number | name
123: Billy,
123: Bill,
123: Bi
*/

I've tried to do this but I'm an absolute beginner and have no idea what to do with raw SQL. Any ideas are welcome!

CodePudding user response：

UNION ALL combine the values from multiple rows into one row, with each column from a different row as a separate record.

SELECT phone_number, name_1 as name FROM Person
UNION ALL
SELECT phone_number, name_2 as name FROM Person
UNION ALL
SELECT phone_number, name_3 as name FROM Person

CodePudding user response：

You can use UNION ALL statement.

SELECT phone_number, name_1
FROM Person

UNION ALL

SELECT phone_number, name_2
FROM Person

UNION ALL

SELECT phone_number, name_3
FROM Person

Please note that UNION ALL will not remove duplicates (in case you have the same names for a single phone_number.