行列转换例子
select max(count(*)) from employees a group by a.job_id,a.manager_id
--6
select o.job_id,
o.manager_id,
max(decode(o.px, 1, o.last_name, null)) name1,
max(decode(o.px, 1, o.salary, null)) salart1,
max(decode(o.px, 2, o.last_name, null)) name2,
max(decode(o.px, 2, o.salary, null)) salart2,
max(decode(o.px, 3, o.last_name, null)) name3,
max(decode(o.px, 3, o.salary, null)) salart3,
max(decode(o.px, 4, o.last_name, null)) name4,
max(decode(o.px, 4, o.salary, null)) salart4,
max(decode(o.px, 5, o.last_name, null)) name5,
max(decode(o.px, 5, o.salary, null)) salart5,
max(decode(o.px, 6, o.last_name, null)) name6,
max(decode(o.px, 6, o.salary, null)) salart6
from (select a.job_id,
a.manager_id,
a.last_name,
a.salary,
row_number() over(partition by a.job_id, a.manager_id order by a.salary desc nulls last) px
from employees a) o
group by o.job_id, o.manager_id

AD_VP 100 Kochhar 17000 De Haan 17000
AC_MGR 101 Higgins 12000
FI_MGR 101 Greenberg 12000
HR_REP 101 Mavris 6500
MK_MAN 100 Hartstein 13000
MK_REP 201 Fay 6000
PR_REP 101 Baer 10000
PU_MAN 100 Raphaely 11000
SA_MAN 100 Russell 14000 Partners 13500 Errazuriz 12000 Cambrault 11000 Zlotkey 10500
SA_REP 145 Tucker 10000 Bernstein 9500 Hall 9000 Olsen 8000 Cambrault 7500 Tuvault 7000
SA_REP 146 King 10000 Sully 9500 McEwen 9000 Smith 8000 Doran 7500 Sewall 7000
SA_REP 147 Vishney 10500 Greene 9500 Marvins 7200 Lee 6800 Ande 6400 Banda 6200
SA_REP 148 Ozer 11500 Bloom 10000 Fox 9600 Smith 7400 Bates 7300 Kumar 6100
SA_REP 149 Abel 11000 Hutton 8800 Taylor 8600 Livingston 8400 Grant 7000 Johnson 6200
ST_MAN 100 Fripp 8200 Weiss 8000 Kaufling 7900 Vollman 6500 Mourgos 5800
AD_ASST 101 Whalen 4400
AD_PRES King 24000
IT_PROG 102 Hunold 9000
IT_PROG 103 Ernst 6000 Austin 4800 Pataballa 4800 Lorentz 4200
PU_CLERK 114 Khoo 3100 Baida 2900 Tobias 2800 Himuro 2600 Colmenares 2500
SH_CLERK 120 Taylor 3200 Fleaur 3100 Geoni 2800 Sullivan 2500
SH_CLERK 121 Sarchand 4200 Bull 4100 Dellinger 3400 Cabrio 3000
SH_CLERK 122 Chung 3800 Dilly 3600 Gates 2900 Perkins 2500
SH_CLERK 123 Bell 4000 Everett 3900 McCain 3200 Jones 2800
SH_CLERK 124 Walsh 3100 Feeney 3000 OConnell 2600 Grant 2600
ST_CLERK 120 Nayer 3200 Mikkilineni 2700 Landry 2400 Markle 2200
ST_CLERK 121 Bissot 3300 Atkinson 2800 Marlow 2500 Olson 2100
ST_CLERK 122 Mallin 3300 Rogers 2900 Gee 2400 Philtanker 2200
ST_CLERK 123 Ladwig 3600 Stiles 3200 Seo 2700 Patel 2500
ST_CLERK 124 Rajs 3500 Davies 3100 Matos 2600 Vargas 2500
AC_ACCOUNT 205 Gietz 8300
FI_ACCOUNT 108 Faviet 9000 Chen 8200 Urman 7800 Sciarra 7700 Popp 6900