Oracle SQL PIVOT Table
У меня есть запрос, в котором я выбираю местоположения, счетчики и т. Д. Вот мой запрос: И я пытаюсь в основном 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;
И вывод такой (ПОЧТИ ТАМ!):
Но это производит нольs Когда я пытался добавить тип. До того, как я добавил типы, он работал нормально, но я мог где-то пропустить синтаксис. Благодарю.