select building_fk as BUILDING_ID, attr_2 as LPDS_ID, attr_3 as FMS_ID from ADDRESS2 where ID = 'AA60424113';
Getting results like this
BUILDING_ID LPDS_ID FMS_ID
AB96609702 13442018 006259784 / 202
I want results in this format
Key Value
BUILDING_ID AB96609702
LPDS_ID 13442018
FMS_ID 006259784 / 202
I used below query, but I dont want to hardcode the fields the column names can be dynamic, and i dont want to use union operator
with main_query(BUILDING_ID,LPDS_ID,FMS_ID) as (select building_fk, attr_2, attr_3 from ADDRESS2 where ID = 'AA60424113'),
child_query_1(key, value) as (select 'BUILDING_ID' , BUILDING_ID from main_query),
child_query_2(key1, value1) as (select 'LPDS_ID' , LPDS_ID from main_query),
child_query_3(key2, value2) as (select 'FMS_ID' , LPFMS_ID from main_query)
select key, value from child_query_1
union
select key1, value1 from child_query_2
union
select key2, value2 from child_query_3;