How to split one column into two using different tables?

Question

I am developing a frontend application in VBA, which is linked to an Oracle database. In Oracle there are the two tables Table1and Table2, which are related to each other by expTypeID and measID:

Table1
expTypeID | measID| val
12 20001 1.1
13 20002 200
13 21043 250
12 29321 0.9
13 29322 150
15 23450 23000

Table2
expTypeID | measID | productID | productName
12 20001 100023 Apple
13 20002 100023 Apple
13 21043 129842 Pear
12 29321 198372 Orange
13 29322 198372 Orange

I would like to get a new table, that for all products contains the values for specific expTypeIDs in different columns, e.g expTypeID = 12 -> ""Density"" and expTypeID = 13 -> ""EModulus"":

GoalTable
productName | productID | Density | EModulus
Apple 100023 1.1 200
Pear 129842 - 250
Orange 198372 0.9 150

I know that it is possible when all the necessary data is available in one table: show-one-column-data-as-two-columns-in-sql (I found the query proposed by mat particularly useful). Is it still possible when the data is present in two tables?

Thanks in advance

Answer 1

WITH
table1 (EXPTYPEID, MEASID, VAL) AS ( 
 SELECT 12, 20001, 1.1 FROM DUAL UNION ALL
 SELECT 13, 20002, 200 FROM DUAL UNION ALL
 SELECT 13, 21043, 250 FROM DUAL UNION ALL
 SELECT 12, 29321, 0.9 FROM DUAL UNION ALL
 SELECT 13, 29322, 150 FROM DUAL UNION ALL
 SELECT 15, 23450, 23000 FROM DUAL 
),
table2 (EXPTYPEID, MEASID, PRODUCTID, PRODUCTNAME) AS (
 SELECT 12, 20001, 100023, 'Apple' FROM DUAL UNION ALL
 SELECT 13, 20002, 100023, 'Apple' FROM DUAL UNION ALL
 SELECT 13, 21043, 129842, 'Pear' FROM DUAL UNION ALL
 SELECT 12, 29321, 198372, 'Orange' FROM DUAL UNION ALL
 SELECT 13, 29322, 198372, 'Orange' FROM DUAL 
),
prapare_data AS (
 SELECT t1.expTypeID, t1.measID, t1.val, t2.productID, t2.productName
 FROM table1 t1
 JOIN table2 t2 ON t1.expTypeID=t2.expTypeID AND t1.measID=t2.measID
)
SELECT productID, productName,
 MAX(CASE WHEN expTypeID = 12 THEN val END) AS Density,
 MAX(CASE WHEN expTypeID = 13 THEN val END) AS EModulus
FROM prapare_data
GROUP BY productID, productName
ORDER BY productID;

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=f683e33fb705a05de3a2d7e704aac634

• JAUGRY: Thanks, the query works. The problem is now that Group By does only include the first density - EModulus pair. In reality I have multiple E-Modulus values (e.g. measured at differnt temperature conditions with condIDs, which are stored in another table) for the same PRODUCTID. Is there a way to print every matching pair and not only the first?
• Akina: @JAUGRY Group in prapare_data (by productID, productName and expTypeID) and aggregate E-Modulus values (val column) into single CSV string in the subquery.