工种 人数 金额
工种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 code
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 wt3num 1 2 3amount 100 200 300
------解决方案--------------------
- SQL code
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 aunion allselect 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
------解决方案--------------------
- SQL code
建这么一张表：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 Aunion 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 Aorder by type1 工种 1 1 1 1 12 金额 6 6 6 6 56