HANA SQL

1.SQL 列转行

上表为源表，如何列转行，实现下表展示

方法一：采用 case when

SELECT 
 	  YEAR
	, MIN(CASE WHEN MONTH = 1 THEN AMOUNT ELSE 0 END) AS M1
	, MIN(CASE WHEN MONTH = 2 THEN AMOUNT ELSE 0 END) AS M2
	, MIN(CASE WHEN MONTH = 3 THEN AMOUNT ELSE 0 END) AS M3
	, MIN(CASE WHEN MONTH = 4 THEN AMOUNT ELSE 0 END) AS M4
FROM TABLE1
GROUP BY YEAR;

方法二：通过map() 函数

SELECT 
	  YEAR
	, MAP(MONTH,'1'，AMOUNT) AS M1
	, MAP(MONTH,'2'，AMOUNT) AS M2
	, MAP(MONTH,'3'，AMOUNT) AS M3
	, MAP(MONTH,'4'，AMOUNT) AS M4
FROM TABLE1
GROUP BY YEAR;

方法三：通过子查询

SELECT 
  	  P2.YEAR
  	, ( SELECT AMOUNT FROM TABLE1 P1 WHERE MONTH = 1 AND P1.YEAR = P2.YEAR ) AS M1
	, ( SELECT AMOUNT FROM TABLE1 P1 WHERE MONTH = 2 AND P1.YEAR = P2.YEAR ) AS M2
	, ( SELECT AMOUNT FROM TABLE1 P1 WHERE MONTH = 3 AND P1.YEAR = P2.YEAR ) AS M3
	, ( SELECT AMOUNT FROM TABLE1 P1 WHERE MONTH = 4 AND P1.YEAR = P2.YEAR ) AS M4
FROM TABLE1 P2 
GROUP BY P2.YEAR;