我所拥有的是一个查询,我在哪里选择位置,计数等.这是我的查询:而且,我试图基本上把我相信的表格PIVOT.我已经研究过PIVOT等,但似乎并没有明确的方法来做到这一点.如果可以指导任何帮助将不胜感激.
更新了查询以包含类型和MT0建议
WITH qry AS ( select Floor, "Mod", Count_Type, Remaining_Counts, Location, Floor || '' || "Mod" || '' || Count_Type || '' || Location as "Unique" from ( select bin_level as Floor, bin_module as "Mod", icqa_process_properties.icqa_process_property_value as Count_Type, count(icqa_processes.icqa_process_id) as Remaining_Counts, CASE when bin_type_name = '14-KIVA-DEEP' then ('KIVA-SHELF') when bin_type_name = '18-KIVA-DEEP' then ('KIVA-SHELF') when bin_type_name = '24-KIVA-DEEP' then ('KIVA-SHELF') when bin_type_name = '30-KIVA-DEEP' then ('KIVA-SHELF') when bin_type_name = '34-KIVA-DEEP' then ('KIVA-SHELF') when bin_type_name = '48-KIVA-DEEP' then ('KIVA-SHELF') when bin_type_name = '48-KIVA-XL' then ('KIVA-SHELF') when bin_type_name = '78-KIVA-TALL' then ('KIVA-SHELF') when bin_type_name = 'PALLET-SINGLE' and usage = '1024' then ('KIVA-PALLET') else 'NON-KIVA' end as Location from icqa_process_locations join bins on bins.bin_id = icqa_process_locations.scannable_id inner join icqa_processes on icqa_processes.icqa_process_id = icqa_process_locations.icqa_process_id inner join icqa_process_properties on icqa_processes.icqa_process_id = icqa_process_properties.icqa_process_id --inner join icqa_count_attempts on icqa_count_attempts.icqa_count_attempt_id = icqa_process_locations.icqa_count_attempt_id where icqa_process_locations.icqa_count_attempt_id is NULL and icqa_processes.process_status = ('Active') and icqa_process_properties.icqa_process_property_value in ('CycleCount', 'SimpleBinCount') group by CASE when bin_type_name = '14-KIVA-DEEP' then ('KIVA-SHELF') when bin_type_name = '18-KIVA-DEEP' then ('KIVA-SHELF') when bin_type_name = '24-KIVA-DEEP' then ('KIVA-SHELF') when bin_type_name = '30-KIVA-DEEP' then ('KIVA-SHELF') when bin_type_name = '34-KIVA-DEEP' then ('KIVA-SHELF') when bin_type_name = '48-KIVA-DEEP' then ('KIVA-SHELF') when bin_type_name = '48-KIVA-XL' then ('KIVA-SHELF') when bin_type_name = '78-KIVA-TALL' then ('KIVA-SHELF') when bin_type_name = 'PALLET-SINGLE' and usage = '1024' then ('KIVA-PALLET') else 'NON-KIVA' end, bin_level, bin_module, icqa_process_properties.icqa_process_property_value order by icqa_process_properties.icqa_process_property_value, Location)) SELECT Count_Type || Location, SUM(CASE when "Mod" = 'dz-P-1A' THEN Remaining_Counts else 0 END ) AS "P-1-A", SUM(CASE when "Mod" = 'dz-P-2A' THEN Remaining_Counts else 0 END ) AS "P-2-A", SUM(CASE when "Mod" = 'dz-R-1T' THEN Remaining_Counts else 0 END ) AS "R-1-T", SUM(CASE when "Mod" = 'dz-R-1F' THEN Remaining_Counts else 0 END ) AS "R-1-F", SUM(CASE when "Mod" = 'dz-R-1O' THEN Remaining_Counts else 0 END ) AS "R-1-O", SUM(CASE when "Mod" = 'dz-P-1B' THEN Remaining_Counts else 0 END ) AS "P-1-B", SUM(CASE when "Mod" = 'dz-P-1D' THEN Remaining_Counts else 0 END ) AS "P-1-D" FROM qry GROUP BY Count_Type || Location;
输出就是这个(几乎在那里!):
但是,当我尝试添加类型时,它正在生成Zero.在我添加类型之前它工作正常,但我可能在某处错过了语法.谢谢.