a slightly complicated UNION ALL problem - SQL

Question

I have a CSV like this:

company_name | Address | person1      | person2     | person3     | ... | person34 |
____________________________________________________________________________________________

stackoverflow| USA     | Brian Guan   |             |             | ... |          |
google       | Cali, US|  John Smith  | James Smith | Ron Swanson | ... | 34th member |

In other words, each row can have more columns depending on if they have more team members. What I want to do is some sort of union all so that all the team members are in one column, but each row still has the necessary company name and address information. In other words, I want to get it like this"

company_name | Address | person      |
_______________________________________
stackoverflow| USA     | Brian Guan   |
google       | Cali, US|  John Smith  |
google       | Cali, US| James Smith  |
google       | Cali, US|  Ron Swanson |
.
.
.
google       | Cali, US|  34th member |

I know the largest team has 34 people, but am having trouble wrapping my head around this. Any help would be appreciated!

Does this answer your question? [MySQL - turn table into different table](https://stackoverflow.com/questions/15184381/mysql-turn-table-into-different-table) — Phil Ross, Jul 09 '20 at 22:00

score 0 · Accepted Answer · answered Jul 09 '20 at 21:54

One method is:

select company_name, address, person1 as person
from t
where person1 is not null
union all
select company_name, address, person2
from t
where person2 is not null
. . .

Just continue for all the columns. You can use a spreadsheet to construct the code, if you don't want to type it all yourself.

score 0 · Answer 2 · answered Jul 09 '20 at 22:07

SQL is written in such a way so to encourage set-based thinking, where you do operations on sets such as union, where, etc, as compared to a language like java, where you tend to do things iteratively - using a for loop or whatever.

What you have here is a data set with a whole lot of columns, and what you want to do is go through and treat each column as its own row. This sort of operation is better suited to an iterative langauge than a set based language like SQL, so maybe you want to use some other language?

Otherwise there are approaches which involve specifying each column manually, like the one linked by Phil or Gordon

a slightly complicated UNION ALL problem - SQL

2 Answers2