使用聚合filter子句。Demo at db<>fiddle:

select first_name,
       id, 
       sum(total)filter(where type='onsite') as onsite,
       sum(total)filter(where type='remote') as remote
from test
group by first_name,
         id;

字符串
| 名字|ID|现场|远程|
| --|--|--|--|
| 乔| 1234 | 13 | 9 |
| 简| 4321 | 1 | 99 |
查看crosstab()：

create extension tablefunc;

select * from crosstab(
   $a$ select first_name, id, type, total from people order by 1,2,3 $a$ , 
   $b$ select distinct type from people order by 1 $b$
  ) AS ct(first_name text, id int, onsite int, remote int);

型

下面是conditional aggregation方法，它很容易理解，如果你只有几个不同的“类型”，IMO推荐使用。

select 
 first_name, 
 id, 
 max(case when type = 'onsite' then total end) as onsite, 
 max(case when type = 'remote' then total end) as remote
from people
group by first_name, id

字符串