使用SQL将列中的层次结构递归地分解为多个列

Question

我有一个存储固定宽度文件数据的单列表。我试图通过以非规范化的形式递归地填充列中的数据，将其解压缩到视图中的多个列中。

下面的4条记录实际上是4条表记录。第一行-标题记录，第2行-报头数据记录，第3行-数据布局记录第4行-数据记录

00 00000000000000000000000000 RX  12/2012 FMH99999 ABCDE                   C  EUR 1212-1212      0000003899001982HAP04
00 00000000000050100001   100100001 Subterritory 100001                        SUBTERRITORY             1000     40      
Z01A00009999900100000000000500IMSIRIN           KAPS                    24          IMSIMSHEALTH         000888888800   
Z01A0000999990011000000    722          +00000000014940+00000006723000+00000000358560000  

我想在这里实现的是以下格式:第一行|第二行|第三行|第四行

这是我到目前为止所尝试的，但是第1行记录没有在所有记录中填充，而只在第一个记录中填充。我正在寻找ANSI SQL方法(数据库不可知论)。

SELECT  SUBSTRING(DATASET, 28, 1)       AS HEADER_RECORD_LAYOUT_TYPE
,   SUBSTRING(DATASET, 29, 2)       AS HEADER_RECORD_DATA_TYPE
,   ''                              AS HEADER_DATA_LEVEL
,   ''                              AS HEADER_DATA_CODE
,   ''                              AS RECORD_LAYOUT_CODE
,   ''                              AS RECORD_LAYOUT_DEFINITION_TYPE
,   ''                              AS DATA_RECORD_SIGN_FOR_COUNTING_UNITS
,   ''                              AS DATA_RECORD_COUNTING_UNITS
FROM    MY_TABLE
WHERE   SUBSTRING(DATASET, 28, 1) = '0' --Line 1
UNION ALL
SELECT  ''                              AS HEADER_RECORD_LAYOUT_TYPE
,   ''                              AS HEADER_RECORD_DATA_TYPE
,   SUBSTRING(DATASET, 17, 2)       AS HEADER_DATA_LEVEL
,   SUBSTRING(DATASET, 19, 9)       AS HEADER_DATA_CODE
,   ''                              AS RECORD_LAYOUT_CODE
,   ''                              AS RECORD_LAYOUT_DEFINITION_TYPE
,   ''                              AS DATA_RECORD_SIGN_FOR_COUNTING_UNITS
,   ''                              AS DATA_RECORD_COUNTING_UNITS
FROM    MY_TABLE
WHERE   SUBSTRING(DATASET, 28, 1) = '1' --Line 2
UNION ALL
SELECT  ''                              AS HEADER_RECORD_LAYOUT_TYPE
,   ''                              AS HEADER_RECORD_DATA_TYPE
,   ''                              AS HEADER_DATA_LEVEL
,   ''                              AS HEADER_DATA_CODE
,   SUBSTRING(DATASET, 1, 5)        AS RECORD_LAYOUT_CODE
,   SUBSTRING(DATASET, 28, 1)       AS RECORD_LAYOUT_DEFINITION_TYPE
,   ''                              AS DATA_RECORD_SIGN_FOR_COUNTING_UNITS
,   ''                              AS DATA_RECORD_COUNTING_UNITS
FROM    MY_TABLE
WHERE   SUBSTRING(DATASET, 28, 1) = '5' --Line 3
UNION ALL
SELECT  ''                              AS HEADER_RECORD_LAYOUT_TYPE
,   ''                              AS HEADER_RECORD_DATA_TYPE
,   ''                              AS HEADER_DATA_LEVEL
,   ''                              AS HEADER_DATA_CODE
,   ''                              AS RECORD_LAYOUT_CODE
,   ''                              AS RECORD_LAYOUT_DEFINITION_TYPE
,   SUBSTRING(DATASET, 71, 1)       AS DATA_RECORD_SIGN_FOR_COUNTING_UNITS
,   SUBSTRING(DATASET, 72, 17)      AS DATA_RECORD_COUNTING_UNITS
FROM    MY_TABLE
SUBSTRING(DATASET, 28, 1) = '4' --Line 4
;

Answer 1

我想这就是你想要的:

SELECT *
FROM (
SELECT  SUBSTRING(DATASET, 28, 1)       AS HEADER_RECORD_LAYOUT_TYPE
,   SUBSTRING(DATASET, 29, 2)       AS HEADER_RECORD_DATA_TYPE
FROM    MY_TABLE
WHERE   SUBSTRING(DATASET, 28, 1) = '0' --Line 1
) header1
CROSS JOIN (
SELECT  SUBSTRING(DATASET, 71, 1)       AS DATA_RECORD_SIGN_FOR_COUNTING_UNITS
,   SUBSTRING(DATASET, 72, 17)      AS DATA_RECORD_COUNTING_UNITS
FROM    MY_TABLE
WHERE   SUBSTRING(DATASET, 28, 1) = '4' --Line 4
) data1
CROSS JOIN (
SELECT  SUBSTRING(DATASET, 1, 5)        AS RECORD_LAYOUT_CODE
,   SUBSTRING(DATASET, 28, 1)       AS RECORD_LAYOUT_DEFINITION_TYPE
FROM    MY_TABLE
WHERE   SUBSTRING(DATASET, 28, 1) = '5' --Line 3
) header2
CROSS JOIN (
SELECT  MIN(CASE WHEN SUBSTRING(DATASET, 28, 1) = '1' THEN SUBSTRING(DATASET, 17, 2) END)   AS HEADER_DATA_LEVEL
,   MIN(CASE WHEN SUBSTRING(DATASET, 28, 1) = '1' THEN SUBSTRING(DATASET, 19, 9) END)   AS HEADER_DATA_CODE
FROM    MY_TABLE
WHERE   SUBSTRING(DATASET, 28, 1) = '2' --Line 2
) data2
;

在计算列SUBSTRING(DATASET, 28, 1)

上建立索引是有益的。