ORACLE行转列
工作中遇到的问题，如下图，需要把所有料号的WL属性（WL_单台净重(KG)、WL_单台毛重(KG)、WL_外箱宽(CM)、WL_外箱重量(KG)、WL_外箱重量(KG)、WL_外箱长(CM)、WL_外箱高(CM)、WL_彩盒宽(CM)、WL_彩盒长(CM)、WL_彩盒高(CM)、WL_整板层数(层)、WL_整板数量(台)、WL_整箱数量(台)、WL_栈板宽(CM)、WL_栈板重量(KG)、WL_栈板长(CM)、WL_栈板高(CM)））输出成列显示
ORACLE行转列可以用两种函数decode 、case when then。
select x.part_number,
sum(decode(x.attribute_name, 'WL_单台净重(KG)', x.attribute_value, null)) WL_单台净重,
sum(decode(x.attribute_name, 'WL_单台毛重(KG)', x.attribute_value, null)) WL_单台毛重,
sum(decode(x.attribute_name, 'WL_外箱宽(CM)', x.attribute_value, null)) WL_外箱宽,
sum(decode(x.attribute_name, 'WL_外箱重量(KG)', x.attribute_value, null)) WL_外箱重量,
sum(decode(x.attribute_name, 'WL_外箱长(CM)', x.attribute_value, null)) WL_外箱长,
sum(decode(x.attribute_name, 'WL_外箱高(CM)', x.attribute_value, null)) WL_外箱高,
sum(decode(x.attribute_name, 'WL_彩盒宽(CM)', x.attribute_value, null)) WL_彩盒宽,
sum(decode(x.attribute_name, 'WL_彩盒长(CM)', x.attribute_value, null)) WL_彩盒长,
sum(decode(x.attribute_name, 'WL_彩盒高(CM)', x.attribute_value, null)) WL_彩盒高,
sum(decode(x.attribute_name, 'WL_整板层数(层)', x.attribute_value, null)) WL_整板层数,
sum(decode(x.attribute_name, 'WL_整板数量(台)', x.attribute_value, null)) WL_整板数量,
sum(decode(x.attribute_name, 'WL_整箱数量(台)', x.attribute_value, null)) WL_整箱数量,
sum(decode(x.attribute_name, 'WL_栈板宽(CM)', x.attribute_value, null)) WL_栈板宽,
sum(decode(x.attribute_name, 'WL_栈板重量(KG)', x.attribute_value, null)) WL_栈板重量,
sum(decode(x.attribute_name, 'WL_栈板长(CM)', x.attribute_value, null)) WL_栈板长,
sum(decode(x.attribute_name, 'WL_栈板高(CM)', x.attribute_value, null))
from bitc_plm_maternumber_attribute x
where x.part_number in('1214-01056','1216-00148')
group by x.part_number
0 Comments