您可以在一个MERGE语句中完成所有操作，方法是使用解析函数查找最低温度行，然后对其他行进行排序并旋转以查找alt. bin位置：

MERGE INTO table_name dst
USING (
  SELECT part_id,
         part_no,
         mfg,
         location,
         MAX(CASE WHEN grp = 1 AND bin_rn = 1 THEN bin_loc END) AS bin_loc,
         MAX(CASE WHEN grp = 2 AND bin_rn = 1 THEN bin_loc END) AS alt_bin_loc1,
         MAX(CASE WHEN grp = 2 AND bin_rn = 2 THEN bin_loc END) AS alt_bin_loc2,
         MAX(CASE WHEN grp = 2 AND bin_rn = 3 THEN bin_loc END) AS alt_bin_loc3,
         MIN(temp_id) AS temp_id
  FROM   (
    SELECT t.*,
           ROW_NUMBER() OVER (
             PARTITION BY part_id, part_no, mfg, location, grp
             ORDER BY bin_loc
           ) AS bin_rn
    FROM   (
      SELECT part_id,
             part_no,
             mfg,
             location,
             bin_loc,
             temp_id,
             LEAST(
               ROW_NUMBER() OVER (
                 PARTITION BY part_id, part_no, mfg, location
                 ORDER BY temp_id
               ),
               2
             ) AS grp
      FROM   table_name t
    ) t
  )
  GROUP BY
         part_id,
         part_no,
         mfg,
         location
) src
ON (    src.part_id  = dst.part_id
    AND src.part_no  = dst.part_no
    AND src.mfg      = dst.mfg
    AND src.location = dst.location)
WHEN MATCHED THEN
  UPDATE
  SET alt_bin_loc1 = src.alt_bin_loc1,
      alt_bin_loc2 = src.alt_bin_loc2,
      alt_bin_loc3 = src.alt_bin_loc3
  DELETE WHERE dst.bin_loc != src.bin_loc;

字符串
其中，对于样本数据：

CREATE TABLE table_name (
  part_id      NUMBER,
  part_no      VARCHAR2(10),
  mfg          VARCHAR2(10),
  location     NUMBER,
  bin_loc      VARCHAR2(10),
  alt_bin_loc1 VARCHAR2(10),
  alt_bin_loc2 VARCHAR2(10),
  alt_bin_loc3 VARCHAR2(10),
  temp_id      NUMBER
);

INSERT INTO table_name (part_id, part_no, mfg, location, bin_loc, temp_id)
SELECT 950, 'A1234', 'MFG1', 75309, 'A07',    123 FROM DUAL UNION ALL
SELECT 950, 'A1234', 'MFG1', 75309, 'LFRONT', 129 FROM DUAL UNION ALL
SELECT 950, 'A1234', 'MFG1', 75309, 'LTOP',   128 FROM DUAL UNION ALL
SELECT 950, 'A1234', 'MFG1', 75309, 'RFRONT', 127 FROM DUAL;

型
然后，在MERGE之后，该表包含：
| 部件ID|零件编号|Mfg|位置|联系我们|ALT_BIN_LOC 1| ALT_BIN_LOC 2| ALT_BIN_LOC 3|温度ID|
| --|--|--|--|--|--|--|--|--|
| 950 |A1234| Mfg1| 75309 |A07| LFRONT| LTOP| RFRONT| 123 |

• 注意：如果你只是想 * 显示 * 透视数据（而不是修改表），那么只需在USING子句中使用SELECT。*

fiddle

不建议将值放在多个列中，如果出现的次数更多，则最终会有许多列。您可以将它们分组到单个列中，并为每个出现的值使用分隔符。您可以使用LISTAGG如下所示，

select part_id, part_no, mfg, location, 
LISTAGG(bin_loc, '; ') WITHIN GROUP (ORDER BY part_id, part_no, mfg, location) as bin_loc
from part_location;

字符串