行转列问题 ，中***&信面试题- 列转行

 

 

 

 
CREATE TABLE your_table_name (
    field1 STRING,
    field2 STRING,
    field3 STRING
);

insert into your_table_name values 
('1' ,'a','b'  ) ,
('1' ,'c','d'  ) ,
('2' ,'a','b'  ) ,
('2' ,'c','d'  ) ;


 
 SELECT 
 field1 , 
   max( CASE WHEN  field2= 'a' or field3= 'a' THEN 'a'   end ) a   ,
   max( CASE WHEN  field2= 'b' or field3= 'b' THEN 'b'   end ) b   ,
   max( CASE WHEN  field2= 'c' or field3= 'c' THEN 'c'   end ) c   ,
   max( CASE WHEN  field2= 'd' or field3= 'd' THEN 'd'   end ) d 
 
 FROM  your_table_name 
 group by field1
 ;
field1    a    b    c    d
1    a    b    c    d
2    a    b    c    d