0

I have a query that returns customer product information that shows which region a product is sourced from.

Query:

SELECT 
    prod.prod_id, 
    prod.is_source,
    link.prod_link_disabled, 
    loc.location_id, 
    loc.customer_id, 
    cust.customer_name
FROM 
    product prod, 
    product_link link, 
    region region, 
    location loc, 
    customer cust
WHERE 
    prod.prod_id = link.prod_id
    AND link.region_id = region.region_id
    AND region.loc_id = loc.loc_id
    AND link.prod_link_deleted = FALSE
    AND loc.customer_id = customer_id
    AND prod.prod_id = 5874;

This returns the following result:

| prod_id             | is_source | prod_link_disabled | location_id | customer_id | customer_name   |
| ------------------- | --------- | ------------------ | ----------- | ----------- | --------------- |
| Test Product - 5874 |         t |                  f |           2 |         332 | Test Playground |
| Test Product - 5874 |         f |                  f |           1 |         332 | Test Playground |

This is showing that product 5874 is coming from location 2 (because is_source is true).

I'd like to compress this information into a single row. I'd like a result that looks like this:

| prod_id             | prod_link_disabled | src_location_id | dest_location_id | customer_id | customer_name   |
| ------------------- | ------------------ | --------------- | ---------------- | ----------- | --------------- |
| Test Product - 5874 |                  f |               2 |                1 |         332 | Test Playground |

This compressed the two previous rows into a single row where src_location_id is 2 (again because that row had is_source as true) and dest_location_id is 1

How can I pivot the two row result into a single row?

It is safe to assume that there are always only two results in the first query.

0

Do I understand correctly and you need something like:

SELECT prod1.prod_id, 
       link1.prod_link_disabled, 
       loc1.location_id AS src_location_id, 
       loc1.location_id AS dst_location_id, 
       loc1.customer_id, 
       cust1.customer_name
  FROM product AS prod1 
  JOIN product_link AS link1 ON link1.prod_id = prod1.prod_id
  JOIN region AS region1 ON region1.region_id = link1.region_id
  JOIN location AS loc1 ON loc1.loc_id = region1.loc_id
  JOIN customer AS cust1 ON cust1.customer_id = loc1.customer_id
  JOIN product AS prod2 ON prod2.prod_id = prod1.prod_id
  JOIN product_link AS link2 ON link2.prod_id = prod2.prod_id
  JOIN region AS region2 ON region2.region_id = link2.region_id
  JOIN location AS loc2 ON loc2.loc_id = region2.loc_id
  JOIN customer AS cust2 ON cust2.customer_id = loc2.customer_id
 WHERE prod1.is_source AND
       NOT link1.prod_link_deleted AND
       NOT prod2.is_source AND
       NOT link2.prod_link_deleted AND
       prod1.prod_id = 5874;

Sure, you can remove excessive JOINs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.