选择子查询以表(Oracle SQL)的形式将多行返回为字符串(一列)

问题描述：

示例我有产品数据:

Product_No  Column1 Column2 ... ColumnX
1           A       10          
2           B       11
3           C       12

对于X列，我需要此表清单中的单个数据行:

And for column X I need single data row from this table Inventory:

Product_No Inventory_No ColumnA ColumnB ColumnC
1           1           ABC     20      30
1           2           DDD     30      50
2           1           EFG     60      70
2           2           CDE     99      100
3           3           EFF     120     30

第x列的结果应为

Product_No  Column1 Column2 ... ColumnX
1           A       10          ABC-20-30,DDD-30-50
2           B       11          EFG-60-70,CDE-99-100
3           C       12          EFF-120-30

如何在不更改主查询联接和返回值的情况下返回该值，我需要子查询才能返回该值.我有尝试list_aggregate，但它可能只适用于我需要从多列合并的一列.谢谢.

How to return that value without altering the main query join and from, i need sub-query to return that value. I have try list_aggregate but it is only possible for one column i need combine from multiple columns. Thank you.

答

SQL提琴

Oracle 11g R2架构设置:

CREATE TABLE Product ( Product_No, Column1, Column2 ) AS
SELECT 1, 'A', 10 FROM DUAL UNION ALL
SELECT 2, 'B', 11 FROM DUAL UNION ALL
SELECT 3, 'C', 12 FROM DUAL
/
CREATE TABLE Inventory ( Product_No, Inventory_No, ColumnA, ColumnB, ColumnC ) AS
SELECT 1, 1, 'ABC',  20,  30 FROM DUAL UNION ALL
SELECT 1, 2, 'DDD',  30,  50 FROM DUAL UNION ALL
SELECT 2, 1, 'EFG',  60,  70 FROM DUAL UNION ALL
SELECT 2, 2, 'CDE',  99, 100 FROM DUAL UNION ALL
SELECT 3, 3, 'EFF', 120,  30 FROM DUAL
/

查询1 :

SELECT p.*, i.ColumnX
FROM   Product p
       LEFT OUTER JOIN
       ( SELECT Product_no,
                LISTAGG(
                  ColumnA || '-' || ColumnB || '-' || ColumnC,
                  ','
                ) WITHIN GROUP ( ORDER BY Inventory_no )
                AS ColumnX
         FROM   Inventory
         GROUP BY Product_No
       ) i
       ON ( p.product_no = i.product_no )

结果 :

Results:

| PRODUCT_NO | COLUMN1 | COLUMN2 |              COLUMNX |
|------------|---------|---------|----------------------|
|          1 |       A |      10 |  ABC-20-30,DDD-30-50 |
|          2 |       B |      11 | EFG-60-70,CDE-99-100 |
|          3 |       C |      12 |           EFF-120-30 |

选择子查询以表(Oracle SQL)的形式将多行返回为字符串(一列)

相关推荐