Intereting Posts

Таблица Oracle SQL PIVOT

У меня есть запрос, где я выбираю местоположения, подсчитывает и т. Д. Вот мой запрос: И я пытаюсь в основном 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. Когда я пытался добавить тип. До того, как я добавил типы, он работал нормально, но я, возможно, пропустил синтаксис где-то. Благодарю.

    Вы можете сделать что-то вроде этого:

    SQL Fiddle

    Настройка схемы Oracle 11g R2 :

     CREATE TABLE tbl ( "mod", "floor", "Remaining Counts", CountType ) AS SELECT 'dz-P-1A', 1, 37, 'CycleCount' FROM DUAL UNION ALL SELECT 'dz-P-1D', 1, 321, 'CycleCount' FROM DUAL UNION ALL SELECT 'dz-P-1T', 1, 16, 'CycleCount' FROM DUAL UNION ALL SELECT 'dz-P-2A', 2, 25, 'CycleCount' FROM DUAL UNION ALL SELECT 'dz-R-1T', 1, 3318, 'CycleCount' FROM DUAL UNION ALL SELECT 'dz-P-1A', 1, 6351, 'SimpleBinCount' FROM DUAL UNION ALL SELECT 'dz-P-1D', 1, 121, 'SimpleBinCount' FROM DUAL UNION ALL SELECT 'dz-P-2A', 2, 12638, 'SimpleBinCount' FROM DUAL UNION ALL SELECT 'dz-R-1F', 1, 68, 'SimpleBinCount' FROM DUAL UNION ALL SELECT 'dz-R-1O', 1, 47, 'SimpleBinCount' FROM DUAL UNION ALL SELECT 'dz-R-1T', 1, 2051, 'SimpleBinCount' FROM DUAL; 

    Запрос 1 :

    PIVOT с использованием SUM( CASE ... ) :

     SELECT CountType, SUM( CASE "mod" WHEN 'dz-P-1A' THEN "Remaining Counts" END ) AS "P-1-A", SUM( CASE "mod" WHEN 'dz-P-2A' THEN "Remaining Counts" END ) AS "P-2-A", SUM( CASE "mod" WHEN 'dz-R-1T' THEN "Remaining Counts" END ) AS "R-1-T", SUM( CASE "mod" WHEN 'dz-R-1F' THEN "Remaining Counts" END ) AS "R-1-F", SUM( CASE "mod" WHEN 'dz-R-1O' THEN "Remaining Counts" END ) AS "R-1-O", SUM( CASE "mod" WHEN 'dz-P-1B' THEN "Remaining Counts" END ) AS "P-1-B", SUM( CASE "mod" WHEN 'dz-P-1D' THEN "Remaining Counts" END ) AS "P-1-D" FROM tbl GROUP BY CountType 

    Результаты :

     | COUNTTYPE | P-1-A | P-2-A | R-1-T | R-1-F | R-1-O | P-1-B | P-1-D | |----------------|-------|-------|-------|--------|--------|--------|-------| | SimpleBinCount | 6351 | 12638 | 2051 | 68 | 47 | (null) | 121 | | CycleCount | 37 | 25 | 3318 | (null) | (null) | (null) | 321 | 

    Запрос 2 :

    Использование транспозиции PIVOT:

     SELECT * FROM ( SELECT SUBSTR( "mod", 4 ) AS "mod", "Remaining Counts", CountType FROM tbl t ) PIVOT ( SUM("Remaining Counts") FOR "mod" IN ( 'P-1A', 'P-2A', 'R-1T', 'R-1F', 'R-1O', 'P-1B', 'P-1D' ) ) 

    Результаты :

     | COUNTTYPE | 'P-1A' | 'P-2A' | 'R-1T' | 'R-1F' | 'R-1O' | 'P-1B' | 'P-1D' | |----------------|--------|--------|--------|--------|--------|--------|--------| | SimpleBinCount | 6351 | 12638 | 2051 | 68 | 47 | (null) | 121 | | CycleCount | 37 | 25 | 3318 | (null) | (null) | (null) | 321 | 

    Изменить – обертка вашего запроса:

     WITH qry AS ( select drop_zone_id as "Mod", bin_level as "Floor", icqa_process_properties.icqa_process_property_value as "Count Type", count(*) as "Remaining Counts", concat(drop_zone_id, icqa_process_properties.icqa_process_property_value) as "Unique", to_char(sysdate,'hh:mi:ssam') as "Time Last Updated", to_char(sysdate, 'MM-DD-YYYY') as "Date Last Updated" from icqa_process_locations 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 bins on bins.bin_id = icqa_process_locations.scannable_id where icqa_count_attempt_id is NULL and icqa_processes.process_status = ('Active') and drop_zone_id not like 'dz-R-1B' and drop_zone_id not like 'dz-P-1Z' and drop_zone_id not like 'dz-P-EACH_1' and icqa_process_properties.icqa_process_property_value in ('CycleCount', 'SimpleBinCount') group by icqa_process_properties.icqa_process_property_value, bin_level, drop_zone_id order by icqa_process_properties.icqa_process_property_value, drop_zone_id ) SELECT "Count Type", SUM( CASE "Mod" WHEN 'dz-P-1A' THEN "Remaining Counts" END ) AS "P-1-A", SUM( CASE "Mod" WHEN 'dz-P-2A' THEN "Remaining Counts" END ) AS "P-2-A", SUM( CASE "Mod" WHEN 'dz-R-1T' THEN "Remaining Counts" END ) AS "R-1-T", SUM( CASE "Mod" WHEN 'dz-R-1F' THEN "Remaining Counts" END ) AS "R-1-F", SUM( CASE "Mod" WHEN 'dz-R-1O' THEN "Remaining Counts" END ) AS "R-1-O", SUM( CASE "Mod" WHEN 'dz-P-1B' THEN "Remaining Counts" END ) AS "P-1-B", SUM( CASE "Mod" WHEN 'dz-P-1D' THEN "Remaining Counts" END ) AS "P-1-D" FROM qry GROUP BY "Count Type"; 

    Если у вас есть доступ к PLSQL в вашем приложении, то «пользовательские агрегированные функции» могут быть хорошим способом. Я обнаружил, что он обеспечивает хороший способ инкапсулировать функции транспонирования и работает очень хорошо. Недавно я написал, что взял огромное количество интервальных данных (строк) и повернул их в столбцы интервала (1-24) по дням (строки). Он действительно занимал много места, но я обнаружил, что код был понятным и простым в обслуживании.

    Вот ссылка на список методов агрегации строк, доступных в Oracle. Один из них использует функцию user_defined. Вы по существу делаете одни и те же вещи, но возвращаете коллекцию столбцов PLSQL вместо агрегированной строки:

    http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php#user_defined_aggregate_function

    Мне также понравилась пользовательская агрегатная функция из-за того, как Oracle обрабатывает параллелизацию производительности. Вот еще doc в Oracle 11g:

    http://docs.oracle.com/cd/B19306_01/appdev.102/b14289/dciaggfns.htm