oracle查询语句数据横向变纵向
关键字: oralce的sql查询语句
Select a.iyear,
a.account_date,
Sum(trancate_value1) trancate_value1,Sum(voucher_value1) voucher_value1,
Sum(trancate_value2) trancate_value2,Sum(voucher_value2) voucher_value2,
Sum(trancate_value3) trancate_value3,Sum(voucher_value3) voucher_value3,
Sum(trancate_value4) trancate_value4,Sum(voucher_value4) voucher_value4,
Sum(trancate_value5) trancate_value5,Sum(voucher_value5) voucher_value5,
Sum(trancate_value6) trancate_value6,Sum(voucher_value6) voucher_value6,
Sum(trancate_value7) trancate_value7,Sum(voucher_value7) voucher_value7,
Sum(trancate_value8) trancate_value8,Sum(voucher_value8) voucher_value8,
Sum(trancate_value9) trancate_value9,Sum(voucher_value9) voucher_value9,
Sum(trancate_value10) trancate_value10,Sum(voucher_value10) voucher_value10,
Sum(trancate_value11) trancate_value11,Sum(voucher_value11) voucher_value11,
Sum(trancate_value12) trancate_value12,Sum(voucher_value12) voucher_value12,
partition_field
From (Select
account_date, --分组
iyear,
partition_field,
Decode(Imonth,1,transact_value) trancate_value1,Decode(Imonth,1,voucher_value) voucher_value1,
Decode(Imonth,2,transact_value) trancate_value2,Decode(Imonth,2,voucher_value) voucher_value2,
Decode(Imonth,3,transact_value) trancate_value3,Decode(Imonth,3,voucher_value) voucher_value3,
Decode(Imonth,4,transact_value) trancate_value4,Decode(Imonth,4,voucher_value) voucher_value4,
Decode(Imonth,5,transact_value) trancate_value5, Decode(Imonth,5,voucher_value) voucher_value5,
Decode(Imonth,6,transact_value) trancate_value6,Decode(Imonth,6,voucher_value) voucher_value6,
Decode(Imonth,7,transact_value) trancate_value7,Decode(Imonth,7,voucher_value) voucher_value7,
Decode(Imonth,8,transact_value) trancate_value8,Decode(Imonth,8,voucher_value) voucher_value8,
Decode(Imonth,9,transact_value) trancate_value9,Decode(Imonth,9,voucher_value) voucher_value9,
Decode(Imonth,10,transact_value) trancate_value10,Decode(Imonth,10,voucher_value) voucher_value10,
Decode(Imonth,11,transact_value) trancate_value11,Decode(Imonth,11,voucher_value) voucher_value11,
Decode(Imonth,12,transact_value) trancate_value12,Decode(Imonth,12,voucher_value) voucher_value12
From t_month
) a
Group By a.account_date,a.iyear,a.partition_field Order By account_date Desc
a.account_date,
Sum(trancate_value1) trancate_value1,Sum(voucher_value1) voucher_value1,
Sum(trancate_value2) trancate_value2,Sum(voucher_value2) voucher_value2,
Sum(trancate_value3) trancate_value3,Sum(voucher_value3) voucher_value3,
Sum(trancate_value4) trancate_value4,Sum(voucher_value4) voucher_value4,
Sum(trancate_value5) trancate_value5,Sum(voucher_value5) voucher_value5,
Sum(trancate_value6) trancate_value6,Sum(voucher_value6) voucher_value6,
Sum(trancate_value7) trancate_value7,Sum(voucher_value7) voucher_value7,
Sum(trancate_value8) trancate_value8,Sum(voucher_value8) voucher_value8,
Sum(trancate_value9) trancate_value9,Sum(voucher_value9) voucher_value9,
Sum(trancate_value10) trancate_value10,Sum(voucher_value10) voucher_value10,
Sum(trancate_value11) trancate_value11,Sum(voucher_value11) voucher_value11,
Sum(trancate_value12) trancate_value12,Sum(voucher_value12) voucher_value12,
partition_field
From (Select
account_date, --分组
iyear,
partition_field,
Decode(Imonth,1,transact_value) trancate_value1,Decode(Imonth,1,voucher_value) voucher_value1,
Decode(Imonth,2,transact_value) trancate_value2,Decode(Imonth,2,voucher_value) voucher_value2,
Decode(Imonth,3,transact_value) trancate_value3,Decode(Imonth,3,voucher_value) voucher_value3,
Decode(Imonth,4,transact_value) trancate_value4,Decode(Imonth,4,voucher_value) voucher_value4,
Decode(Imonth,5,transact_value) trancate_value5, Decode(Imonth,5,voucher_value) voucher_value5,
Decode(Imonth,6,transact_value) trancate_value6,Decode(Imonth,6,voucher_value) voucher_value6,
Decode(Imonth,7,transact_value) trancate_value7,Decode(Imonth,7,voucher_value) voucher_value7,
Decode(Imonth,8,transact_value) trancate_value8,Decode(Imonth,8,voucher_value) voucher_value8,
Decode(Imonth,9,transact_value) trancate_value9,Decode(Imonth,9,voucher_value) voucher_value9,
Decode(Imonth,10,transact_value) trancate_value10,Decode(Imonth,10,voucher_value) voucher_value10,
Decode(Imonth,11,transact_value) trancate_value11,Decode(Imonth,11,voucher_value) voucher_value11,
Decode(Imonth,12,transact_value) trancate_value12,Decode(Imonth,12,voucher_value) voucher_value12
From t_month
) a
Group By a.account_date,a.iyear,a.partition_field Order By account_date Desc
实例 :select box_id,
max(confirmMaterielId),
max(appMaterielId),
sum(goodStoreCount),
sum(doaStoreCount),
sum(faultStoreCount)
from (select t.box_id,
t.materiel_id,
decode(sum(t.fault_count), 0, t.materiel_id, '') confirmMaterielId,
decode(sum(t.good_count),
0,
decode(sum(t.doa_count), 0, t.materiel_id, ''),
'') appMaterielId,
sum(t.good_count) goodStoreCount,
sum(t.doa_count) doaStoreCount,
sum(t.fault_count) faultStoreCount
from op_back_matchinginfo_box t
where t.box_id = '135-1'
group by t.box_id, t.materiel_id)
group by box_id
max(confirmMaterielId),
max(appMaterielId),
sum(goodStoreCount),
sum(doaStoreCount),
sum(faultStoreCount)
from (select t.box_id,
t.materiel_id,
decode(sum(t.fault_count), 0, t.materiel_id, '') confirmMaterielId,
decode(sum(t.good_count),
0,
decode(sum(t.doa_count), 0, t.materiel_id, ''),
'') appMaterielId,
sum(t.good_count) goodStoreCount,
sum(t.doa_count) doaStoreCount,
sum(t.fault_count) faultStoreCount
from op_back_matchinginfo_box t
where t.box_id = '135-1'
group by t.box_id, t.materiel_id)
group by box_id