基于Table1条件关联2-3表实现单行转多行数据提取

Let's break this down into two straightforward steps: first converting your wide Table1 into a row-based structure (since each codeid/codetype pair is a separate record in your target output), then handling the conditional joins based on the codetype value. Since C-Tree DB supports UNION and WITH clauses, we can leverage those to make this work smoothly.

Step 1: Unpivot Table1 (Wide to Narrow) #

Since there's no built-in UNPIVOT function, we'll use UNION ALL to split each set of codeidN/codetypeN columns into individual rows. We'll also filter out rows where codeid is 0 (since those don't map to any valid record in Table2/Table3, per your description).

Step 2: Conditional Joins for Description #

• When codetype = 0: Directly join Table2 using codeid as the matching ID.
• When codetype = 1: First join Table3 on codeid, then link to Table2 using table2id from Table3.

Here's the full SQL query:

WITH unpivoted_table1 AS (
    -- First code pair
    SELECT 
        main_id, -- Replace with your actual Table1 primary key/identifier
        codeid1 AS codeid,
        codetype1 AS codetype
    FROM Table1
    WHERE codeid1 != 0
    UNION ALL
    -- Second code pair
    SELECT 
        main_id,
        codeid2 AS codeid,
        codetype2 AS codetype
    FROM Table1
    WHERE codeid2 != 0
    -- Repeat this block for codeid3-codetype3 up to codeid20-codetype20
    UNION ALL
    -- 20th code pair
    SELECT 
        main_id,
        codeid20 AS codeid,
        codetype20 AS codetype
    FROM Table1
    WHERE codeid20 != 0
)
SELECT 
    ut.main_id,
    ut.codeid,
    ut.codetype,
    -- Pick the correct description based on codetype
    COALESCE(t2.description, t2_from_t3.description) AS description
FROM unpivoted_table1 ut
-- Join Table2 directly when codetype is 0
LEFT JOIN Table2 t2 
    ON ut.codetype = 0 AND ut.codeid = t2.id
-- Join Table3 first when codetype is 1, then link to Table2
LEFT JOIN Table3 t3 
    ON ut.codetype = 1 AND ut.codeid = t3.id
LEFT JOIN Table2 t2_from_t3 
    ON t3.table2id = t2_from_t3.id
-- Optional: Filter out rows where no description was found
WHERE COALESCE(t2.description, t2_from_t3.description) IS NOT NULL;

Key Notes: #

• Replace main_id: Swap this with whatever unique identifier your Table1 uses (like a primary key) to keep track of which original row each unpivoted record came from.
• Including codeid=0: If you need to keep rows where codeid is 0 (even though they won't have a description), just remove all the WHERE codeidN != 0 clauses and adjust the final WHERE condition if needed.
• Testing: Run this with a small sample of data first to confirm that the joins are pulling the correct descriptions for both codetype 0 and 1 cases.

内容的提问来源于stack exchange，提问作者Alan