Good Afternoon,
I'm looking to convert multiple records into a single row for a number of items.
Below is an example of a few tables and the desired results that I am looking for.
ORDER
ORDER_ID ORDER_TYPE
10001 1
10002 2
ORDER_ATTRIBUTE
ORDER_ID ATTRIBUTE VALUE
10001 ExtraCost1 1.54
10001 ExtraCost2 2.31
10002 ExtraCost1 .65
10002 ExtraCost2 8.44
I am looking to create a report that will return 1 record for each order above, however, include a column to store the VALUE associated to the ExtraCost1, and another column to store the VALUE associated to the ExtraCost2.
Below are the desired results to report.
FINAL_REPORT
ORDER_ID COST_1 COST_2 ORDER_TYPE
10001 1.54 2.31 1
10002 .65 8.44 2
I can't say that I am all that familiar with the PIVOT function, however, I can't imagine that that is a possible solution here.
I have also attempted some CASE WHEN statements (CASE WHEN return 2 records for each order), and also attempted to include 2 ORDER_ATTRIBUTE tables (joined), specifying the ATTRIBUTE values for each (sample below), but this did not return any results.
Not sure as to what other options are available, but wanted to get some insight from the experts. Thanks in advance for your help!
SELECT
O.ORDER_ID,
OA1.VALUE AS COST_1,
OA2.VALUE AS COST_2,
O.ORDER_TYPE
FROM
ORDER O,
ORDER_ATTRIBUTE OA1,
ORDER_ATTRIBUTE OA2
WHERE
O.ORDER_ID = OA1.ORDER_ID
AND OA1.ORDER_ID = OA2.ORDER_ID
AND OA1.VALUE = 'ExtraCost1'
AND OA2.VALUE = 'ExtraCost2';