MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table a (
  單號 varchar(50),
  金額 float
);
insert into a(單號,金額) 
values('i001',100);
insert into a(單號,金額) 
values('i002',200);
insert into a(單號,金額) 
values('i003',300);
insert into a(單號,金額) 
values('o123',333);
insert into a(單號,金額) 
values('o223',444);
insert into a(單號,金額) 
values('o323',555);
select *
from a


單號
金額


i001
100

i002
200

i003
300

o123
333

o223
444

o323
555


select
單號,
CASE WHEN LEFT(單號,1)='i' THEN 金額 ELSE 0 END 收入金額,
CASE WHEN LEFT(單號,1)='o' THEN 金額 ELSE 0 END 支出金額
from a


單號
收入金額
支出金額


i001
100
0

i002
200
0

i003
300
0

o123
0
333

o223
0
444

o323
0
555