基于条件的SQL行转列（Pivot）问题求助

需求说明：将adj_product_type为BPS的行转换为新列，对应到同id下所有BPM类型的行中；已知每个id的BPM行可能有多条，BPS行仅一条。

示例数据 #

id    adj_product_type  dti_adj
--    ----------------  -------
1     BPM               DTI_GT45_LTE50
1     BPM               DTI_GT45
1     BPS               ABC

期望输出 #

id  adj_product_type  dti_adj          adj_product_type_2  dti_adj_2
--  ----------------  -------          ------------------  ---------
1   BPM               DTI_GT45_LTE50   BPS                 ABC
1   BPM               DTI_GT45

建表及测试数据SQL #

create table abc1 (id number,adj_product_type varchar2(100), dti_adj varchar2(100));
insert into abc1 values(1,'BPM','DTI_GT45_LTE50');
insert into abc1 values(1,'BPM','DTI_GT45');
insert into abc1 values(1,'BPS','ABC');

解决方案 #

方法1：左连接查询

利用BPS每个id仅一条的特性，将BPM行与同id的BPS行左连接，直接获取对应列值：

SELECT
    bpm.id,
    bpm.adj_product_type,
    bpm.dti_adj,
    bps.adj_product_type AS adj_product_type_2,
    bps.dti_adj AS dti_adj_2
FROM abc1 bpm
LEFT JOIN abc1 bps
    ON bpm.id = bps.id
    AND bps.adj_product_type = 'BPS'
WHERE bpm.adj_product_type = 'BPM'

方法2：窗口函数实现

通过MAX(CASE...)窗口函数，在同id范围内提取BPS的对应值，无需关联：

SELECT
    id,
    adj_product_type,
    dti_adj,
    MAX(CASE WHEN adj_product_type = 'BPS' THEN adj_product_type END) OVER (PARTITION BY id) AS adj_product_type_2,
    MAX(CASE WHEN adj_product_type = 'BPS' THEN dti_adj END) OVER (PARTITION BY id) AS dti_adj_2
FROM abc1
WHERE adj_product_type = 'BPM'

原方法问题说明 #

你之前使用的LEAD函数仅能获取当前行的下一条记录，当BPM有多条时，只有最后一条BPM的下一行是BPS，其他BPM行的LEAD会取到下一条BPM的值，无法实现所有BPM行都关联BPS列的需求。

内容的提问来源于stack exchange，提问作者Sri Goverdhan Sam