SQL语句多行合并为一行

name            orderid              price              type

张三                 0001                15                 buy

李四                 0001                15                 sell

 

要变成

buyer          seller            price                  orderid 

张三             李四               15                       0001

 select orderid,price,max(buyer) buyer,max(seller) seller from (

select case when type='buy' then name end as 'buyer',

case when type='sell' then name end  as 'seller',

orderid,price from table_test ) group by orderid,price