Oracle使用多种数据类型进行切换(Oracle unpivot with multiple data types)

Oracle使用多种数据类型进行切换(Oracle unpivot with multiple data types) ID VT_Type1 Vt_type2 VT_Type3 Status_1 Status_2 Status_3 Date_1 Date_2 Date_3 1 -1 -1 0 X Y 2 04/12 05/12 06/12 2 -1 -1 -1 A B 1 06/12 07/12 07/10

预期产出

Id Type Status Date 1 1 X 04/12 1 2 Y 05/12 2 1 A 06/12 2 2 B 07/12 2 3 1 07/10

我可以通过引用这个多案例中的答案获得预期结果SQL查询将单行检索为多列但在我的表中我引用的列具有不同的数据类型。 就像VT_Type3是-1那么我应该从Status_3读取数据，其中数字是其他2列是varchar2的数字。

ID VT_Type1 Vt_type2 VT_Type3 Status_1 Status_2 Status_3 Date_1 Date_2 Date_3 1 -1 -1 0 X Y 2 04/12 05/12 06/12 2 -1 -1 -1 A B 1 06/12 07/12 07/10

Expected output

Id Type Status Date 1 1 X 04/12 1 2 Y 05/12 2 1 A 06/12 2 2 B 07/12 2 3 1 07/10

I could get the expected result by referring the answer in this multiple case SQL query retrieve single row as multiple column but in my table i referring columns have different data types. Like if VT_Type3 is -1 then i should read data from Status_3 which is of number where as other 2 columns is of varchar2.

最满意答案

使用子查询，首先，将Status_3列转换为与其他列相同的类型：

WITH test_data ( ID, VT_Type1, Vt_type2, VT_Type3, Status_1, Status_2, Status_3, Date_1, Date_2, Date_3 ) AS ( SELECT 1, -1, -1, 0, 'X', 'Y', 2, '04/12', '05/12', '06/12' FROM DUAL UNION ALL SELECT 2, -1, -1, -1, 'A', 'B', 1, '06/12', '07/12', '07/10' FROM DUAL ) SELECT id, type, status, dt AS "DATE" FROM ( SELECT ID, VT_Type1, Vt_type2, VT_Type3, Status_1, Status_2, TO_CHAR( Status_3 ) AS status_3, Date_1, Date_2, Date_3 FROM test_data UNPIVOT ( (vt_type, status, dt) FOR type IN ( ( vt_type1, status_1, date_1 ) as 1, ( vt_type2, status_2, date_2 ) as 2, ( vt_type3, status_3, date_3 ) as 3 ) ) WHERE vt_type != 0;

Use a sub-query to, first, convert the Status_3 column to the same type as the others:

WITH test_data ( ID, VT_Type1, Vt_type2, VT_Type3, Status_1, Status_2, Status_3, Date_1, Date_2, Date_3 ) AS ( SELECT 1, -1, -1, 0, 'X', 'Y', 2, '04/12', '05/12', '06/12' FROM DUAL UNION ALL SELECT 2, -1, -1, -1, 'A', 'B', 1, '06/12', '07/12', '07/10' FROM DUAL ) SELECT id, type, status, dt AS "DATE" FROM ( SELECT ID, VT_Type1, Vt_type2, VT_Type3, Status_1, Status_2, TO_CHAR( Status_3 ) AS status_3, Date_1, Date_2, Date_3 FROM test_data UNPIVOT ( (vt_type, status, dt) FOR type IN ( ( vt_type1, status_1, date_1 ) as 1, ( vt_type2, status_2, date_2 ) as 2, ( vt_type3, status_3, date_3 ) as 3 ) ) WHERE vt_type != 0;