sql_行轉列

sql 行轉列的一種寫法：

select effectiveDate,
	max(case when rate_type=1 then rate when rate_type=2 then null else null end) as rate1,
	max(case when rate_type=1 then null when rate_type=1 then rate else null end) as rate2
	from table_name
	group by effectiveDate;
           

資料樣例：

RATE_TYPE	RATE	EFFECTIVE_DATE
1			1.11	2015-01-01
2			1.22	2015-01-01
1			2.11	2015-01-02
2			2.23	2015-01-02
1			5.33	2016-03-03
2			6.22	2016-03-03

....
           

結果:

EFFECTIVE_DATE	RATE1	RATE2
2015-01-01		1.11	1.22
2015-01-02		2.11	2.23
2016-03-03		5.33	6.22