I have a series of records that have multiple alternate bin definitions (BIN_LOC).

For example:

I have a query that works on this when there is only one alternate location. When there are more than one, it fails. I've tried several things such as using IN and replacing EXISTS, using RANK and PARTITION without any success. I have to consolidate thousands of rows with varying numbers of matching PART_NO, MFG, and LOCATION records with different BIN_LOC values.

update PART_LOCATION a
    set alt_bin_loc_1 = 
    (
     select bin_loc
     from PART_LOCATION b where a.part_no = b.part_no and a.mfg = b.mfg and a.location = b.location and a.bin_loc <> b.bin_loc
     and a.temp_id < b.temp_id
    )
    where exists
    (
     select bin_loc
     from PART_LOCATION b where a.part_no = b.part_no and a.mfg = b.mfg and a.location = b.location and a.bin_loc <> b.bin_loc
     and a.temp_id < b.temp_id
    );

What I need to accomplish to consolidate the ALT_BIN_XXX locations with the matching records on the PART_ID/PART_NO, MFG, LOCATION on the main record with the lowest TEMP_ID and then delete the record(s) with the next BIN_LOC not matching. As in the following progression.

First Iteration results:

Second Iteration Results:

Third Iteration results:

CodePudding user response：

It is not advisable to have the values in multiple columns, if you have more number of occurrences, you will end up having many columns. You can group them into a single column and have a seperator for each occurrence. You can do that using LISTAGG as below,

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;

CodePudding user response：

You can do it all in a single MERGE statement by using analytic functions to find the minimum temperature row and then ordering the other rows and pivoting to find the alt. bin locations:

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;

Which, for the sample data:

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;

Then, after the MERGE the table contains:

Note: If you just want to display the pivoted data (rather than modifying the table) then just use the SELECT in the USING clause.

fiddle