Oracle SQL PIVOT表

发布时间：2021-01-21 06:24:00 所属栏目：站长百科来源：网络整理

导读：我所拥有的是一个查询,我在哪里选择位置,计数等. 这是我的疑问：而且,我试图基本上把我相信的桌子PIVOT.我已经研究过PIVOT等,但似乎并没有明确的方法来做到这一点.如果可以指导任何帮助将不胜感激. 更新了查询以包含类型和MT0建议 WITH qry AS (select Floor

SELECT * FROM (
   SELECT SUBSTR( "mod",4 ) AS "mod",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",'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,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";

（编辑：辽源站长网）

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

3/3

首页