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 |