Oracle SQL PIVOT表 [英] Oracle SQL PIVOT Table
本文介绍了Oracle SQL PIVOT表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我所拥有的是一个查询，可以在其中选择位置，数量等. 这是我的查询:而且，我正在尝试基本PIVOT我相信的表.我已经研究过PIVOT之类的东西，但是似乎没有明确的方法可以做到这一点.如果可以提供任何帮助，将不胜感激.
What I have is a query, where I am selecting locations, counts, etc. Here is my query: And, I am trying to basically PIVOT the tables I believe. I have looked into PIVOT and such, but it doesn't seem like there is a clear cut way to do it. If any help can be guided would be greatly appreciated.
更新后的查询包含类型和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;
输出是这个(ALMOST THERE！):
And the output is this(ALMOST THERE!):
但是，当我尝试添加类型时，它会产生零.在添加类型之前，它可以正常工作，但是我可能在某些地方错过了语法.谢谢.
But, it is producing Zero's When I tried to add type. Before I added the types it worked fine, but I may have missed syntax somewhere. Thanks.
推荐答案
您可以执行以下操作:
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 ... )语句的PIVOT:
PIVOT using SUM( CASE ... ) statements:
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 :
使用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' )
)
结果 :
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";
这篇关于Oracle SQL PIVOT表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
