问将不同列填充到一个列中的oracle查询

EN

select id, provider as name, 'provider' from telecom union all
select id, consumer, 'consumer' from telecom union all
select id, receiver, 'receiver' from telecom ;

select t.id,
       (case which when 'provider' then provider
                   when 'consumer' then consumer
                   when 'receiver' then receiver
        end) as name,
       which                  
from telecom t cross join
     (select 'provider' as which from dual union all
      select 'consumer' as which from dual union all
      select 'receiver' as which from dual
     ) x

SET DEFINE OFF;

CREATE TABLE Telecom ( id, provider, consumer, receiver ) AS
SELECT 1, 'vodaphone', 't-mobile', 'AT&T' FROM DUAL;

SELECT id,
       name,
       "type"
FROM   telecom
UNPIVOT( name FOR "type" IN ( provider, consumer, receiver ) );

       ID NAME      type   
---------- --------- --------
         1 vodaphone PROVIDER 
         1 t-mobile  CONSUMER 
         1 AT&T      RECEIVER 

SET DEFINE OFF;

CREATE TABLE Telecom ( id, col_provider, col_consumer, col_receiver ) AS
SELECT 1, 'vodaphone', 't-mobile', 'AT&T' FROM DUAL UNION ALL
SELECT 1, 't-mobile',  NULL, 'EE' FROM DUAL;

SELECT id,
       name,
       "type"
FROM   telecom
UNPIVOT( name FOR "type" IN ( col_provider AS 'provider',
                              col_consumer AS 'consumer',
                              col_receiver AS 'receiver' ) );

        ID NAME      type   
---------- --------- --------
         1 vodaphone provider 
         1 t-mobile  consumer 
         1 AT&T      receiver 
         1 t-mobile  provider 
         1 EE        receiver