Making all tables results a child(in 1 columns) for a Parent ID

Question

I have 1 table with 5 columns, and I need to create another tabled with just 2 columns based off of a key ID in the original table, where the the column is not blank.

The data in each of the columns is always the same per column.

Example:

    ID | Col1 | Col2 | Col3 | Col4 |
    1  |   A  |   B  |   -  |   -  |
    2  |   -  |   B  |   -  |   D  |
    3  |   A  |   -  |   C  |   D  |

required output in new table:

    ID | Child |
    1  |   A   |
    1  |   B   |
    2  |   B   |
    2  |   D   |
    3  |   A   |
    3  |   C   |
    3  |   D   |

I can't seem to get my head around this, although I feel like there is something really simple I am missing, and that I need to be joining the table to itself, but its the putting all of the results into a single column that is stumping me. Any help would be appreciated, and please feel free to ask any questions.

Also, is there a name for this process I am trying to do, please?

• Will i need to create a query to select each 'ID + columnX' into a table individually, where I always input X into the new 'Child' column?

Answer 1

The "standard" SQL method would use union all:

select id, col1 as col from t where col1 is not null union all
select id, col2 from t where col2 is not null union all
select id, col3 from t where col3 is not null union all
select id, col4 from t where col4 is not null union all
select id, col5 from t where col5 is not null;

That said, this is not the most efficient method, because it scans the table five times (once for each column). This is particularly bad if the "table" is really a view.

There are alternative methods, but they tend to depend on the database.