Two columns combined "as" one in SQL

Question

I have three columns

Field A = A,B,C 
Field B = D,E,F  
Field C = G,H,I

How can I combine them into one single row WITHOUT using "union all"?

Like this:

select Field A as 1 from TableZ 
union all 
select Field B as 1 from TableZ
union all 
select Field C as 1 from TableZ

The reason I do not want to use a union is because the real query I am making is big, so I don't want to repeat the code 3 times. Important to say is that I do not wish to concat the three columns, I just want to add them in single row.

Answer 1

If you don't want to repeat your query - use Common Table Expressions, commonly known as CTE.

Here is an example:

WITH t1 as (
  -- My very long ang complicated query
  SELECT my_fields
  FROM TableZ 
)
select FieldA as f1 from t1 
union all 
select FieldB as f1 from t1 
union all 
select FieldC as f1 from t1 

Answer 2

Assuming you want to transpose those columns instead of "combine them into one single row" (see also my comment), I offer an alternative to the solution of markalex:

with original_query as (
  SELECT FieldA, FieldB, FieldC
  FROM TableZ 
),
distribute (discriminator) as (
  select 1 from rdb$database
  union all select 2 from rdb$database
  union all select 3 from rdb$database
)
select 
  case discriminator 
    when 1 then FieldA
    when 2 then FieldB
    when 3 then FieldC
  end as "1"
from original_query
cross join distribute

Here the helper CTE distribute is cross-joined with your original query, and you then use the values of distribute.discriminator to decide which column to show.

dbfiddle: https://dbfiddle.uk/A6asW5iZ