根据某些条件在select中需要额外的行

Question

我有一个列的表: attribute_1、attribute_2、org_name、country

attribute_1  attribute_2  org_name  country
wcc_4599        null        org1     IN
wcc_0123        wcc_1983    org2     IN
wcc_2390        null        org3     US
wcc_5647        wcc_8789    org4     IN

我的要求是：

1. 根据以下条件，我必须决定document_type (i)如果国家=印度，attribute_1 != null，attribute_2 = null，那么文档类型==> PAN (ii)如果==> =印度，attribute_1 != null，attribute_2 != null，那么文档类型==> PAN，独资DOCUMNENT (iii)如果国家!=印度，attribute_1 != null，attribute_2 = null然后文档类型==>公司注册文档
2. 对于(ii)情况，我需要为每条记录显示两行，第一行用于attribute_1，第二行用于attribute_2

我有以下问题

SELECT   
     attribute_1,
     attribute_2,
     upper(org_name) org_name,                      
     country,
CASE
    WHEN country = 'IN'
         AND attribute_1 IS NOT NULL
         AND attribute_2 IS NULL THEN 'PAN'
    WHEN country = 'IN'
         AND attribute_1 IS NOT NULL
         AND attribute_2 IS NOT NULL THEN 'PAN , PROPRIETORSHIP DOCUMNENT'
    WHEN country != 'IN'
         AND attribute_1 IS NOT NULL
         AND attribute_2 IS NULL THEN 'COMPANY REGISTRAION DOCUMENT'     
END
doc_name
            FROM
                tbl_1
            ORDER BY
                created_date_time;

产出：

attribute_1  attribute_2  org_name  country doc_type
wcc_4599        null        org1     IN      PAN
wcc_0123        wcc_1983    org2     IN      PAN, PROPRIETORSHIP DOCUMENT
wcc_2390        null        org3     US      COMPANY REGISTRAION DOCUMENT
wcc_5647        wcc_8789    org4     IN      PAN, PROPRIETORSHIP DOCUMENT

但我需要这样的输出：

attribute_1_2  org_name country doc_type
wcc_4599        org1     IN      PAN
wcc_0123        org2     IN      PAN
wcc_1983        org2     IN      PROPRIETORSHIP DOCUMENT
wcc_2390        org3     US      COMPANY REGISTRAION DOCUMENT
wcc_5647        org4     IN      PAN
wcc_8789        org4     IN      PROPRIETORSHIP DOCUMENT

目前，我已经创建了一个获得所需输出的过程。但我想通过查询来做这件事。有可能吗？

谢谢!

Answer 1

我认为这里缺少一个表，它可以将CASE表达式的标量输出映射到一个或多个行。请考虑使用以下CTE，并将当前查询放入CTE：

WITH doc_type AS (
    SELECT 1 AS id, 'PAN' AS type FROM dual UNION ALL
    SELECT 2, 'PAN' FROM dual UNION ALL
    SELECT 2, 'PROPRIETORSHIP DOCUMNENT' FROM dual UNION ALL
    SELECT 3, 'COMPANY REGISTRAION DOCUMENT' FROM dual
),
cte AS (
    SELECT
        attribute_1,
        attribute_2,
        UPPER(org_name) org_name,
        country,
        created_date_time
    CASE WHEN country = 'IN' AND
              attribute_1 IS NOT NULL AND
              attribute_2 IS NULL THEN 1
         WHEN country = 'IN' AND
              attribute_1 IS NOT NULL AND
              attribute_2 IS NOT NULL THEN 2
         WHEN country != 'IN' AND
              attribute_1 IS NOT NULL AND
              attribute_2 IS NULL THEN 3 END AS doc_id
    FROM tbl_1
)

现在，我们可以从CASE表达式中加入当前计算列，以生成您期望的记录：

SELECT
    t1.attribute_1,
    t1.attribute_2,
    t1.org_name,
    t1.country,
    t2.type
FROM cte t1
INNER JOIN doc_type t2
    ON t1.doc_id = t2.id
ORDER BY
    t1.created_date_time;