请教一个sql，用一个sql写出来
工种人数金额
工种116
工种216
工种316
工种416
工种5156
工种6146455
工种71456
工种810
工种910
程汝梅10



希望变成
工种工种1工种2工种3工种4工种5
人数11111
金额666655


假设表为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