工种 人数 金额
工种1 1 6
工种2 1 6
工种3 1 6
工种4 1 6
工种5 1 56
工种6 1 46455
工种7 1 456
工种8 1 0
工种9 1 0
程汝梅 1 0
希望变成
工种 工种1 工种2 工种3 工种4 工种5
人数 1 1 1 1 1
金额 6 6 6 6 55
假设表为a
工种1 1 6
工种2 1 6
工种3 1 6
工种4 1 6
工种5 1 56
工种6 1 46455
工种7 1 456
工种8 1 0
工种9 1 0
程汝梅 1 0
希望变成
工种 工种1 工种2 工种3 工种4 工种5
人数 1 1 1 1 1
金额 6 6 6 6 55
假设表为a
SQL> WITH t AS (
2 SELECT 'wt1' work_type,1 num,100 amount FROM DUAL UNION ALL
3 SELECT 'wt2' work_type,2 num,200 amount FROM DUAL UNION ALL
4 SELECT 'wt3' work_type,3 num,300 amount FROM DUAL UNION ALL
5 SELECT 'wt4' work_type,4 num,400 amount FROM DUAL UNION ALL
6 SELECT 'wt5' work_type,5 num,500 amount FROM DUAL UNION ALL
7 SELECT 'wt6' work_type,6 num,600 amount FROM DUAL
8 )
9 SELECT 'work_type' work_type,
10 'wt1' wt1,
11 'wt2' wt2,
12 'wt3' wt3
13 FROM DUAL
14 UNION ALL
15 SELECT 'num' num,
16 TO_CHAR(MAX(DECODE(t.work_type, 'wt1', t.num))) wt1,
17 TO_CHAR(MAX(DECODE(t.work_type, 'wt2', t.num))) wt2,
18 TO_CHAR(MAX(DECODE(t.work_type, 'wt3', t.num))) wt3
19 FROM t
20 UNION ALL
21 SELECT 'amount' amount,
22 TO_CHAR(MAX(DECODE(t.work_type, 'wt1', t.amount))) wt1,
23 TO_CHAR(MAX(DECODE(t.work_type, 'wt2', t.amount))) wt2,
24 TO_CHAR(MAX(DECODE(t.work_type, 'wt3', t.amount))) wt3
25 FROM t
26 ;WORK_TYPE WT1 WT2 WT3
--------- ---------------------------------------- ---------------------------------------- ----------------------------------------
work_type wt1 wt2 wt3
num 1 2 3
amount 100 200 300
select
sum(decode(工种,'工种1',1,0)) "工种1",
sum(decode(工种,'工种2',1,0)) "工种2",
sum(decode(工种,'工种3',1,0)) "工种3",
sum(decode(工种,'工种4',1,0)) "工种4",
sum(decode(工种,'工种5',1,0)) "工种5"
from a
union all
select
sum(decode(工种,'工种1',金额,0)) "工种1",
sum(decode(工种,'工种2',金额,0)) "工种2",
sum(decode(工种,'工种3',金额,0)) "工种3",
sum(decode(工种,'工种4',金额,0)) "工种4",
sum(decode(工种,'工种5',金额,0)) "工种5"
from a
建这么一张表:
create table A(
work_type varchar2(40),
person_num number,
money number
)
统计sql如下:
select
'工种' type,
sum(decode(work_type,'工种1',person_num,0)) "工种1",
sum(decode(work_type,'工种2',person_num,0)) "工种2",
sum(decode(work_type,'工种3',person_num,0)) "工种3",
sum(decode(work_type,'工种4',person_num,0)) "工种4",
sum(decode(work_type,'工种5',person_num,0)) "工种5"
from A
union
select
'金额' type,
sum(decode(work_type,'工种1',money,0)) "工种1",
sum(decode(work_type,'工种2',money,0)) "工种2",
sum(decode(work_type,'工种3',money,0)) "工种3",
sum(decode(work_type,'工种4',money,0)) "工种4",
sum(decode(work_type,'工种5',money,0)) "工种5"
from A
order by type1 工种 1 1 1 1 1
2 金额 6 6 6 6 56