Oracle SQL枢轴表

Question

我所拥有的是一个查询，在这里我选择位置、计数等。这里是我的查询:并且，我试图基本上将我相信的表枢轴化。我已经研究过枢轴和诸如此类的东西，但似乎没有明确的方法来做到这一点。如果能提供任何帮助，我们将不胜感激。

更新的查询，包括类型和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。在我添加这些类型之前，它工作得很好，但我可能遗漏了语法。谢谢。

Answer 1

你可以这样做：

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

使用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

Results

|      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

使用枢轴转位：

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' )
)

Results

|      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";

Answer 2

如果您可以在应用程序中访问PLSQL，那么“用户定义的聚合函数”可能是一个很好的方法。我发现它提供了一种很好的方法来封装转置功能，并且执行得很好。我最近写了一篇文章，它提取了大量的间隔数据(行)，并将它们按日(行)排列成间隔(1-24行)的列。它确实需要大量的编码，但我发现代码是清晰的，易于维护。

以下是指向Oracle中可用的字符串聚合技术列表的链接。其中之一使用user_defined函数。实际上，您执行的是相同的操作，但是返回一个列的PLSQL集合，而不是一个聚合字符串：

function

我还喜欢用户定义的聚合函数，因为Oracle为了提高性能而处理并行化。下面是Oracle 11g中的更多文档：

01/appdev.102/b14289/dciaggfns.htm