加入收藏 | 设为首页 | 会员中心 | 我要投稿 威海站长网 (https://www.0631zz.cn/)- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置: 首页 > 站长百科 > 正文

Oracle SQL PIVOT表

发布时间:2021-01-21 09:24:27 所属栏目:站长百科 来源:网络整理
导读:我所拥有的是一个查询,我在哪里选择位置,计数等. 这是我的疑问:而且,我试图基本上把我相信的桌子PIVOT.我已经研究过PIVOT等,但似乎并没有明确的方法来做到这一点.如果可以指导任何帮助将不胜感激. 更新了查询以包含类型和MT0建议 WITH qry AS (select Floor
副标题[/!--empirenews.page--]

我所拥有的是一个查询,我在哪里选择位置,计数等.
这是我的疑问:而且,我试图基本上把我相信的桌子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',321,'CycleCount' FROM DUAL
UNION ALL SELECT 'dz-P-1T',16,'CycleCount' FROM DUAL
UNION ALL SELECT 'dz-P-2A',2,25,'CycleCount' FROM DUAL
UNION ALL SELECT 'dz-R-1T',3318,'CycleCount' FROM DUAL
UNION ALL SELECT 'dz-P-1A',6351,'SimpleBinCount' FROM DUAL
UNION ALL SELECT 'dz-P-1D',121,'SimpleBinCount' FROM DUAL
UNION ALL SELECT 'dz-P-2A',12638,'SimpleBinCount' FROM DUAL
UNION ALL SELECT 'dz-R-1F',68,'SimpleBinCount' FROM DUAL
UNION ALL SELECT 'dz-R-1O',47,'SimpleBinCount' FROM DUAL
UNION ALL SELECT 'dz-R-1T',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

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换位:

(编辑:威海站长网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

热点阅读