主页 / dba / 问题 / 301383
Accepted
JAUGRY
Asked: 2021-10-20 22:57:07 +0800 CST

如何使用不同的表将一列分成两列？

  • 772

我正在用 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 提出的查询特别有用）。当数据存在于两个表中时是否仍有可能？

提前致谢

oracle vba

1 个回答

  1. Best Answer
    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

    • 0

相关问题