sql查询帮助透视表

Question

嘿，我的问题是这样的，这可能对其他人也很有用，我有一个带有instance_id的订单，该订单有几个ticket_number与sr_num不同的票据，但都有相同的服务实例id。票证可能处于打开、关闭或进行中的不同阶段，假设一个订单有4张票证，并且有许多具有4张或更少票证的订单，我找到一种票证类型的所有打开的票证，获取其service_instance_id并与其相对应，找到该订单的其他打开的票证。

使用以下查询，我可以找到它，但它都在另一个下面，我希望它针对特定订单，所有4个票证信息都在一行中，如果该订单的特定票证已关闭，则不应显示

 select a.sr_num Acceptance_TT, a.act_open_dt Acceptance_Date, e.login Acceptance_Owner , c.sr_area, c.sr_num, c.act_open_dt, d.login, case when c.sr_stat_id in ('Closed','Cancelled') then '-' else c.sr_stat_id end TT_Status
    from 
    siebel.s_srv_req a -- GOC TT
    ,siebel.s_order_item b -- ServiceInstanceID
    ,siebel.s_srv_req c -- Project Management
    ,siebel.s_user d -- Owner
    ,siebel.s_user e -- GOC Owner
    where 
    a.sr_area = 'GOC Acceptance' and a.sr_num='20130601-3-28'
    and a.sr_stat_id in ('Open - GOC')
    and a.x_service_instance_id = b.row_id
    and c.x_service_instance_id = b.row_id
    and c.sr_area = 'Project Management'
    --and c.sr_stat_id not in ('Closed','Cancelled')
    and c.owner_emp_id = d.row_id
    and a.owner_emp_id = e.row_id  PM 5:39 
    UNION all
    select a.sr_num Acceptance_TT, a.act_open_dt Acceptance_Date, e.login Acceptance_Owner, c.sr_area, c.sr_num, c.act_open_dt, d.login, case when c.sr_stat_id in ('Closed','Cancelled') then '-' else c.sr_stat_id end TT_Status
    from 
    siebel.s_srv_req a -- GOC TT
    ,siebel.s_order_item b -- ServiceInstanceID
    ,siebel.s_srv_req c -- Provisioning
    ,siebel.s_user d -- Owner
    ,siebel.s_user e -- GOC Owner
    where 
    a.sr_area = 'GOC Acceptance' and a.sr_num='20130601-3-28'
    and a.sr_stat_id in ('Open - GOC')
    and a.x_service_instance_id = b.row_id
    and c.x_service_instance_id = b.row_id
    and c.sr_area = 'Provisioning'
    --and c.sr_stat_id not in ('Closed','Cancelled')
    and c.owner_emp_id = d.row_id
    and a.owner_emp_id = e.row_id  PM 5:39 
    UNION all
    select a.sr_num Acceptance_TT, a.act_open_dt Acceptance_Date, e.login Acceptance_Owner, c.sr_area, c.sr_num, c.act_open_dt, d.login, case when c.sr_stat_id in ('Closed','Cancelled') then '-' else c.sr_stat_id end TT_Status
    from 
    siebel.s_srv_req a -- GOC TT
    ,siebel.s_order_item b -- ServiceInstanceID
    ,siebel.s_srv_req c -- CE Implementation
    ,siebel.s_user d -- Owner
    ,siebel.s_user e -- GOC Owner
    where 
    a.sr_area = 'GOC Acceptance' and a.sr_num='20130601-3-28'
    and a.sr_stat_id in ('Open - GOC')
    and a.x_service_instance_id = b.row_id
    and c.x_service_instance_id = b.row_id
    and c.sr_area = 'CE Implementation'
    --and c.sr_stat_id not in ('Closed','Cancelled')
    and c.owner_emp_id = d.row_id
    and a.owner_emp_id = e.row_id  

Answer 1

尝试并共享结果，我没有您的配置可供测试

with tab as (
    select a.sr_num Acceptance_TT, 
           a.act_open_dt Acceptance_Date, 
           e.login Acceptance_Owner, 
           c.sr_area, 
           c.sr_num, 
           c.act_open_dt, 
           d.login, 
           case when c.sr_stat_id in ('Closed','Cancelled') then '-' else c.sr_stat_id end TT_Status
    from 
    siebel.s_srv_req a -- GOC TT
    ,siebel.s_order_item b -- ServiceInstanceID
    ,siebel.s_srv_req c -- CE Implementation
    ,siebel.s_user d -- Owner
    ,siebel.s_user e -- GOC Owner
    where 
    a.sr_area = 'GOC Acceptance' and a.sr_num='20130601-3-28'
    and a.sr_stat_id in ('Open - GOC')
    and a.x_service_instance_id = b.row_id
    and c.x_service_instance_id = b.row_id
    and c.sr_area in ('CE Implementation', 'Project Management', 'Provisioning')
    and c.owner_emp_id = d.row_id
    and a.owner_emp_id = e.row_id
    )
 select * 
   from tab 
  pivot (min(TT_Status) as TT_Status for (sr_area) in ('CE Implementation' as CE, 'Project Management' as PM, 'Provisioning' as PRO))