选择子查询以表(Oracle SQL)的形式将多行返回为字符串(一列) [英] Select subquery that return multiple rows as string (one column) in table (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)的形式将多行返回为字符串(一列)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！