PostgreSQL：行变列、非空（CASE WHEN、COALESCE）语句总结_postgres coalesce

1. 源表结构：

|order_id| class| count|
|–|–|–|–|–|
| 111101100002 | 3 |0| 0|3
| 111101100012 | 1|3| 0|3
| 11110112002 | 1|2| 0|3
| 111101100202 | 2 |0| 0|3

2. 目标表结构：

根据order_id分组，根据不同的class进行统计

|order_id| count_1| count_2 || count_3| count_4 || count_5| count_6 || count_7| count_8 || count_9| count_10 |
|–|–|–|–|–|–|–|–|–|–|–|–|–|–|–|–|
| 111101100002 | 3 |0| 0|3 |0| 0|3 |0| 0|3 |0| 0|3 |0| 0|3 |0| 0|3 |0| 0|3 |0| 0|3 |0| 0|3 |0| 0|

---

3. sql语句如下：

（1）分组统计，
（2）使用 COALESCE 返回第一个不为null的值，如果值为null，返回0

select order_id,
sum(COALESCE(cass class when 1 then count END,0)) as count_1,
sum(COALESCE(cass class when 2 then count END,0)) as count_2,
sum(COALESCE(cass class when 3 then count END,0)) as count_3,
sum(COALESCE(cass class when 4 then count END,0)) as count_4,
sum(COALESCE(cass class when 5 then count END,0)) as count_5,
sum(COALESCE(cass class when 6 then count END,0)) as count_6,
sum(COALESCE(cass class when 7 then count END,0)) as count_7,
sum(COALESCE(cass class when 8 then count END,0)) as count_8,
sum(COALESCE(cass class when 9 then count END,0)) as count_9,
sum(COALESCE(cass class when 10 then count END,0)) as count_10
from test_table group by order_id;
1
2
3
4
5
6
7
8
9
10
11
12

4. IF ELSE不太好用，可以用CASE WHEN THEN ELSE代替

比如 active_hour int 值分别为0~23
但是想要得到俩位的HH的小时值，需要把 0~9 转换为 00 ~ 09

select active_hour,case when active_hour > 9 then ''||active_hour else '0'||active_hour from t_active;
1