使用oracle sql将行转换为列

Question

我正在尝试使用以下示例将行转换为列:

<表类> 级COL_VALUETABLE_SRCtbody><<tr>16INT: ADDRESS_LINE_2:空INT16基础:ADDRESS_LINE_2: X基地17INT: ADDRESS_LINE_3:空INT17基础:ADDRESS_LINE_3: X基地

您可以在内部子查询中包含LVL:

SELECT *
FROM   (
SELECT  BATCH_ID
,CONTACT_ID
,COL_VALUE
,TABLE_SRC
,LVL
FROM   MISMATCH
) PIVOT (
max(COL_VALUE)
FOR TABLE_SRC IN ('1BASE' BASE, '1INT' INT)
)

如果您不希望它出现在输出中，则将SELECT *更改为列列表。

对于您的样本数据:

CREATE TABLE mismatch (batch_id, contact_id, LVL, COL_VALUE, TABLE_SRC) AS
SELECT 1, 1, 16, 'INT: ADDRESS_LINE_2:NULL', '1INT'  FROM DUAL UNION ALL
SELECT 1, 1, 16, 'BASE: ADDRESS_LINE_2:X',   '1BASE' FROM DUAL UNION ALL
SELECT 1, 1, 17, 'INT: ADDRESS_LINE_3:NULL', '1INT'  FROM DUAL UNION ALL
SELECT 1, 1, 17, 'BASE: ADDRESS_LINE_3:X',   '1BASE' FROM DUAL;

输出:

<表类>BATCH_IDCONTACT_ID级基地INTtbody><<tr>1116基础:ADDRESS_LINE_2: XINT: ADDRESS_LINE_2:空1117基础:ADDRESS_LINE_3: XINT: ADDRESS_LINE_3:空