如何同时实现Pivot与类Cross Join以构建目标数据表？

Hey there! Let's break down how to solve this problem properly—your cross join approach didn't work because it creates an unfiltered Cartesian product, which isn't what we need here. Instead, we'll use multiple left joins to match each month-offset condition while preserving all rows from T1 and correctly expanding duplicate entries in T2.

The Core Idea #

We'll join T1 to T2 multiple times, each time targeting a specific month offset (0 months, -1 month, -2 months, etc.). When T2 has multiple rows for the same ID and OLD_DATE, the left join will automatically create separate rows in T3 for each combination of matching entries across the different month offsets—exactly the "cross matching" you need.

Example SQL Query (Oracle Syntax) #

Since you used ADD_MONTHS, I'll assume Oracle, but I'll note adjustments for other databases too:

SELECT
    t1.ID,
    t1.REF_DATE,
    t2_0.VALUE AS VALUE_0M,
    t2_1.VALUE AS VALUE_1M,
    t2_2.VALUE AS VALUE_2M
    -- Add more columns here for additional month offsets (e.g., t2_3.VALUE AS VALUE_3M)
FROM T1
-- Join for 0 months offset (same date as T1.REF_DATE)
LEFT JOIN T2 t2_0
    ON t2_0.ID = t1.ID
    AND t2_0.OLD_DATE = t1.REF_DATE
-- Join for -1 month offset
LEFT JOIN T2 t2_1
    ON t2_1.ID = t1.ID
    AND t2_1.OLD_DATE = ADD_MONTHS(t1.REF_DATE, -1)
-- Join for -2 months offset
LEFT JOIN T2 t2_2
    ON t2_2.ID = t1.ID
    AND t2_2.OLD_DATE = ADD_MONTHS(t1.REF_DATE, -2)
-- Add more LEFT JOIN clauses here if you need more month offsets

Adjustments for Other Databases #

• SQL Server: Replace ADD_MONTHS(date, n) with DATEADD(MONTH, n, date)
• PostgreSQL: Replace ADD_MONTHS(date, n) with date + INTERVAL 'n months' (e.g., t1.REF_DATE - INTERVAL '1 month')

How It Handles Duplicate T2 Entries #

Let's use sample data to see this in action:

• T1: One row ID=1, REF_DATE='2023-10-01'
• T2:
  • ID=1, OLD_DATE='2023-10-01', VALUE='A'
  • ID=1, OLD_DATE='2023-10-01', VALUE='B'
  • ID=1, OLD_DATE='2023-09-01', VALUE='X'
  • ID=1, OLD_DATE='2023-09-01', VALUE='Y'

The query will return:

This is exactly what you asked for: duplicate entries in T2 are split into separate rows, and each is cross-matched with entries from other month offsets. If there's no match for a month offset, the column will show NULL as required.

Why Cross Join Failed #

A cross join pairs every row in T1 with every row in T2, regardless of ID or date matches. This creates way more rows than needed, and you can't map the values to the correct VALUE_XM columns properly. The left join approach keeps the matching logic targeted to each column, ensuring only relevant rows are combined.

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