질문

필요한 데이터를 보여주는 아래 작업을 수행했습니다.

그러나 여러 행에 데이터를 표시합니다. 나는 모든 것이 같은 줄에 응축되기를 바랍니다.

select t.fqn,
case when t.stat = 'ACTIVE' then t.stat_count end as Active,
case when t.stat = 'RELEASED' then t.stat_count end as Released,
case when t.stat = 'ALLOCATED' then t.stat_count end as Allocated,
case when t.stat = 'ALLOCATING' then t.stat_count end as Allocating,
case when t.stat = 'PASSIVE' then t.stat_count end as Passive
from
(
select l.fullyqualifiedname as fqn, pg.controllerstatus as stat ,  count (pg.id) as stat_count
from   task pg
left join packgroup pgt
on pgt.id = pg.id
left join location l
on l.id = pg.EXECUTIONLOCATIONID
left join location l2
on l2.id = pg.locationid
where  pg.TASKTYPEKEY = task_def.TASKTYPE_PACKGROUP
and pg.controllerstatus ! = 'EXECUTED'
group by    l.fullyqualifiedname    , pg.controllerstatus   )  t

답변1

다음 코드를 사용하여 작동합니다.

select *
    from (
    select
    SUBSTR(l.fullyqualifiedname,5,10) as fqn,
    p.planningstatus,
    packgroupTask.controllerstatus as controllerstatus
    from
        Task packgroupTask
           left join PackGroup packgroup on packgroup.id = packgroupTask.id
           left join location l on l.id = packgroupTask.executionLocationId
           left join location l2 on l2.id = packgroupTask.locationId
           full join palletizeroverview p on SUBSTR(p.moduleid,-6,10) = SUBSTR(l.fullyqualifiedname,8,10)
        where
           packgroupTask.taskTypeKey = TASK_DEF.TASKTYPE_PACKGROUP
           and packgroupTask.controllerstatus ! = 'EXECUTED')
    pivot (count(controllerstatus)
         for controllerstatus
          in ('ACTIVE' ACTIVE,
              'RELEASED' RELEASED,
              'ALLOCATED' ALLOCATED,
              'ALLOCATING' ALLOCATING,
              'PASSSIVE' PASSIVE))
order by fqn

답변2

이 구문이 올바른지 확실하지 않으며 테스트 할 올바른 장소가 없습니다. 그래서 그것은 단지 아이디어입니다. Pivot을 사용하십시오.

select 
   l.fullyqualifiedname as fqn,
   pg.controllerstatus as stat,
   pg.id as pg_id
from
   task pg left join packgroup pgt on pgt.id = pg.id
   left join location l on l.id = pg.EXECUTIONLOCATIONID
   left join location l2 on l2.id = pg.locationid
where
   pg.TASKTYPEKEY = task_def.TASKTYPE_PACKGROUP
   and pg.controllerstatus ! = 'EXECUTED'
PIVOT
(
    COUNT(pg_id) 
    FOR g.controllerstatus
    IN (  'ACTIVE', 'RELEASED', 'ALLOCATED', 'ALLOCATING', 'PASSIVE' )
)