SQL Query: Column Name as Value I have a Redshift table that looks like the following:

+----------------+---------------+------+
| FieldA         | field1        |field2|
+----------------+---------------+------+
| ABC            | 123           |   78 |
| DEF            | 456           |   90 |
+----------------+---------------+------+


I want to run a query to produce the following output. I believe I have to use a 

UNION ALL 

to get the fields I want split up into different rows, but I'm not sure how to get the column names (field1, field2) as values within a column. 

+----------------+---------------+------+
| FieldA         | value         |field |
+----------------+---------------+------+
| ABC            | 123           |field1|
| DEF            | 456           |field1|
| ABC            | 78            |field2|
| DEF            | 90            |field2|
+----------------+---------------+------+


(I don't care about output order at the moment)
You are right about UNION or UNION ALL. Due to the field column, the legs in the UNION will be disjoint:

SELECT FieldA, field1 as value, 'field1' as field
FROM T
UNION ALL
SELECT FieldA, field2 as value, 'field2' as field
FROM T


I don't think MySQL as of latest version yet supports LATERAL, but I may be mistaken so in case it does, it will be slightly more efficient to do something like:

SELECT x.FieldA, x.value, x.field
FROM T
LATERAL JOIN (
    values (T.FieldA, field1, 'field1')
         , (T.FieldA, field2, 'field2')
) x (FieldA, value, field);


You may have to do:

LATERAL JOIN (
    SELECT T.FieldA, field1 as value, 'field1' as field
    UNION ALL
    SELECT T.FieldA, field2 as value, 'field2' as field
) x;