行转列啊~

参考

https://blog.csdn.net/weixin_45817985/article/details/138699278

impala不像其它数据库提供了PIVOT关键字,那么方便，你参考类似下面的语句吧

SELECT 

    salesperson,

    MAX(CASE WHEN month = 'Jan' THEN amount ELSE 0 END) AS Jan,

    MAX(CASE WHEN month = 'Feb' THEN amount ELSE 0 END) AS Feb,

    MAX(CASE WHEN month = 'Mar' THEN amount ELSE 0 END) AS Mar,

    -- 继续添加其他月份...

FROM sales

GROUP BY salesperson

方式二

WITH aggregated_sales AS (

  SELECT 

      salesperson,

      COLLECT_LIST(month) AS months,

      COLLECT_LIST(amount) AS amounts

  FROM sales

  GROUP BY salesperson

)

SELECT 

  salesperson,

  months[0] AS month1,

  amounts[0] AS amount1,

  months[1] AS month2,

  amounts[1] AS amount2,

  -- 根据需要添加更多的列...

FROM aggregated_sales;

你直接查一行的union吧

使用case when+聚合函数就可以了。如下：