I have an existing table that has many rows something like this:

There are other columns, but this demonstrates the import ones for this question.

It is used as a kind of telephone directory in a php front end, Many of the numbers are the same, espcially for similar products. I need to create a new table that I can show some additional info about each number on the front end, and the current table is very long and has many phone numbers in it in total so it's not practical for me to manually look at it and find all the numbers I weant to put into a new table.

I need to run some kind of select query to get a list of all the unique numbers accross any and all phone number columns the current table has. This is just a one off query to get me a list I can then create a new table with, so I don't care about query efficency.

The result should look something like this:

Just listing all unique numbers. I can then take this data and put it into this new table and start adding other columns myself for the additional information I mentioned, then just use a typical query to show this extra data on my front end when one of the numbers from the orignal table is selected by a user, kind of using the phone number as an ID in my new table.

I have tried to search for a solution and I already know about the DISTINCT command, but my understanding of this is that it will only produce a list of results where every column is not identical, which is not what I want, and this is the search results I seem to get from my keywords.

Once I have a unique list and I have not had to manually look through the orignal large table to get them, I'm fine to manally create the new table and manually insert all the unique numbers by hand, but bonus points if I can also somehow combine the query to insert into a new table I have created. (If this is even possible?)

CodePudding user response：

Based on you sample you could use a select union with one select for each column you need

select tel1 
from my_table
union 
select tel2
from my_table
union
select tel3
from my_table
union 
select tel4
from my_table

then you could create the table using a create select

create table my_new_table 
select tel1 tel
from my_table
union 
select tel2
from my_table
union
select tel3
from my_table
union 
select tel4
from my_table

CodePudding user response：

Assuming the table name is products, you can use the following query

select tel1 as numbers from products
union
select tel2 from products
union
select tel3 from products
union
select tel4 from products;

It will output the following:

View on DB Fiddle

Mandatory PSA:

• Please, when you create a new table, please normalize the table to make life easier.