CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Julaayi
Julaayi

Reputation: 509

Break the Hierarchy in a column into multiple columns recursively using SQL

I have single column table that is storing a fixed width file data. I am trying to unpack it into multiple columns in a View by recursively populating the data in the columns in a de-normalized form.

The 4 records below are actually four table records. Line 1 - Header Record, Line 2 - Header Data Record, Line 3 - Data Layout Record, Line 4 - Data Record

  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

What I am trying to achieve here is in the following Format: Line 1 | Line 2 | Line 3 | Line 4

Here is what I tried so far but the Line 1 records doesn't get populated in all the records but only in first record. I am looking for ANSI SQL approach (database agnostic).

    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
;

Upvotes: 1

Views: 189

Answers (1)

Charlieface
Charlieface

Reputation: 71586

I think this is what you want:

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
;

It would be beneficial to have an index on a computed column SUBSTRING(DATASET, 28, 1)

Upvotes: 1

Related Questions