如何使用不同的表將一列分成兩列？

我正在用 VBA 開發一個前端應用程序，它連結到一個 Oracle 數據庫。在 Oracle 中有兩個表Table1和Table2，它們通過 expTypeID 和 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

我想獲得一個新表，所有產品都包含不同列中特定 expTypeID 的值，例如 expTypeID = 12 -> ““Density”” 和 expTypeID = 13 -> ““EModulus”"：

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

我知道當所有必要的數據都在一個表中可用時是可能的：show-one-column-data-as-two-columns-in-sql（我發現 mat 提出的查詢特別有用）。當數據存在於兩個表中時是否仍有可能？

提前致謝

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

引用自：https://dba.stackexchange.com/questions/301383