Multiple rows with one duplicate value to columns

Question

The ORG_ITEM table contains duplicate IDs. These ids show different MFR and PART_NO for the same ID. This results in my query returning multiple rows with the same ID. How can I get those rows into a single row with each MFR and PART_NO as a separate column?

SELECT
PART_LOC.CLASS,
PART_LOC.ID,
ORG.MFR,
ORG_ITEM.PART_NO,
PART_LOC.STORAGE_LOC,
PART_LOG.QTY,
COST.UNIT_COST

FROM
PART_LOC
LEFT JOIN COST on PART_LOC.ID = COST.ID
LEFT JOIN ORG_ITEM on PART_LOC.ID = ORG_ITEM.ID
LEFT JOIN ORG on ORG_ITEM.MFR = ORG.MFR

WHERE
PART_LOC.STORAGE_LOG = :Facility

Currently returns:

CLASS   ID      MFR      PART_NO   STORAGE_LOC  QTY    COST
  3    1234   Grainger    123F6       CRIB       6    12.67
  3    1234   Hagerty     asd45       CRIB       6    12.67
  3    1234     MSC       poi98       CRIB       6    12.67

Want it to return:

CLASS   ID   MFR-PART_NO1   MFR-PART_NO2   MFR-PART_NO3  STORAGE_LOC  QTY COST
  3    1234 Grainger-123F6  Hagerty-asd45    MSC-poi98      CRIB       6  12.67

Use http://rextester.com/l/oracle_online_compiler Then add Looking for at bottom with comments. Then repost link. Setup a table with some data in it too — JGFMK, Jul 13 '17 at 12:47
Not everybody has access to external image sites: if you post screenshots instead of typing text you reduce the number of people who might be able to help you. Although in this case it doesn't matter, as this problem is an old chestnut. Please search for `oracle + pivot`, you'll find something which will help you — APC, Jul 13 '17 at 12:47
I was able to re-edit and convert links to images. @Brco learn the editor it has insert image too... — JGFMK, Jul 13 '17 at 12:56
If the number of manufacturers is not known ahead of time, you can't do this with standard SQL. You can with dynamic SQL, which is quite different (despite the name) and generally considered a poor practice. WHY do you need the result in that format? What is wrong with your current output? — , Jul 13 '17 at 13:18
@mathguy There are ~12k different ID numbers. If there are multiple rows per ID number, it balloons to ~50k rows. Any ID number could have 1 MFR - PART_NO or multiple (Up to 5). Trying to analyze inventory values, usage, locations. I could throw it in a pivot table, but having one query for employees to run simplifies things. — Bcro1, Jul 19 '17 at 12:04
OK, so that answers a few questions. Is **up to 5** manufacturers per ID guaranteed? You need an upper bound for the number of manufacturers (which become columns in your desired output); you cannot write a query where you don't know from the start how many columns there will be. So that's good. What is bad is the reason to pivot: if 50k rows is too many for a person to look at (I agree that is indeed the case), how is looking at 12k rows any better? Did you **ever** look at 12k rows of anything? At **all** of them? — , Jul 19 '17 at 13:01
@mathguy 12k is definitely not manageable, but better than 50k. When your customer has somewhat unrealistic expectations, you try to accommodate. Customer is always right. From the 12k we can filter on other columns to identify priority and what will result in the biggest savings. Its a start....I will give the below query a try and let you know how it comes out. Thanks again! — Bcro1, Jul 20 '17 at 14:20

score 0 · Answer 1 · answered Jul 19 '17 at 13:12

You could try something like this. For each ID I assume there are at most five manufacturers. I also assumed you don't care in what order the manufacturers will be shown. If you do care (for example if you want them arranged alphabetically from left to right in each row), you can add the ordering criteria in the ROW_NUMBER() function I added to the inner query; right now it shows "order by null" (which means you are indifferent to the order).

If there are more than five manufacturers per ID, this query will still work; only five manufacturers (out of six or eight or ten) will be shown in the output.

select class, id, mfr_part1, mfr_part2, mfr_part3, mfr_part4, mfr_part5, 
       storage_loc, qty, unit_cost
from   (
  SELECT
  PART_LOC.CLASS,
  PART_LOC.ID,
  ORG.MFR,
  ORG_ITEM.PART_NO,
  PART_LOC.STORAGE_LOC,
  PART_LOG.QTY,
  COST.UNIT_COST,
  row_number() over (partition by part_loc.id order by null) as RN
  FROM
  PART_LOC
  LEFT JOIN COST on PART_LOC.ID = COST.ID
  LEFT JOIN ORG_ITEM on PART_LOC.ID = ORG_ITEM.ID
  LEFT JOIN ORG on ORG_ITEM.MFR = ORG.MFR
  WHERE
  PART_LOC.STORAGE_LOG = :Facility
  )
pivot ( max(mfr || '-' || part_no)
        for RN in (1 as mfr_part1, 2 as mfr_part2, 3 as mfr_part3,
                   4 as mfr_part4, 5 as mfr_part5)
      )
;

Multiple rows with one duplicate value to columns

1 Answers1