如何用SQL将扁平CSV转换为SAP HANA DB规范化表结构

Got it, let's break this down. You need to convert your wide-format table (with separate columns for each half-year period) into a normalized, narrow-format table where each row represents one employee's data for a single time period. This is a classic unpivoting task, and SQL is the most straightforward way to handle this in SAP HANA.

Step 1: Assume Your Imported Table Structure #

First, let's say you've imported your CSV into a SAP HANA table named EMPLOYEE_SALARY_EVALS. The columns (as per your CSV) would be:

• employee_id
• salary delta 1.1.2016 - 30.6.2016
• salary delta 1.7.2016 - 21.12.2016
• personal_evaluation 1.1.2016 - 30.6.2016
• personal_evaluation 1.7.2016 - 21.12.2016

(Note: Columns with spaces or special characters need to be wrapped in double quotes in SAP HANA SQL to avoid syntax errors.)

Step 2: Use UNION ALL to Unpivot the Data #

The simplest, most compatible approach (works across all SAP HANA versions) is to use UNION ALL to combine the data from each period into separate rows. Here's the full SQL query:

SELECT
    employee_id,
    TO_DATE('2016-01-01', 'YYYY-MM-DD') AS start_date,
    TO_DATE('2016-06-30', 'YYYY-MM-DD') AS end_date,
    "salary delta 1.1.2016 - 30.6.2016" AS salary_delta,
    "personal_evaluation 1.1.2016 - 30.6.2016" AS personal_evaluation_delta
FROM EMPLOYEE_SALARY_EVALS

UNION ALL

SELECT
    employee_id,
    TO_DATE('2016-07-01', 'YYYY-MM-DD') AS start_date,
    TO_DATE('2016-12-21', 'YYYY-MM-DD') AS end_date,
    "salary delta 1.7.2016 - 21.12.2016" AS salary_delta,
    "personal_evaluation 1.7.2016 - 21.12.2016" AS personal_evaluation_delta
FROM EMPLOYEE_SALARY_EVALS

How This Works: #

• The first SELECT block pulls all data for the January-June 2016 period, mapping the specific columns to your target structure.
• The UNION ALL combines this with the second SELECT block, which does the same for the July-December 2016 period.
• We use TO_DATE() to ensure the date columns are properly formatted as date types (instead of strings) in SAP HANA.

Optional: Use SAP HANA's UNPIVOT Syntax (For HANA 2.0+) #

If you're running SAP HANA 2.0 or later, you can use the built-in UNPIVOT operator for a more concise query:

SELECT
    employee_id,
    TO_DATE(SPLIT_PART(period, ',', 1), 'YYYY-MM-DD') AS start_date,
    TO_DATE(SPLIT_PART(period, ',', 2), 'YYYY-MM-DD') AS end_date,
    salary_delta,
    personal_evaluation_delta
FROM (
    SELECT
        employee_id,
        "salary delta 1.1.2016 - 30.6.2016" AS sal_1,
        "salary delta 1.7.2016 - 21.12.2016" AS sal_2,
        "personal_evaluation 1.1.2016 - 30.6.2016" AS eval_1,
        "personal_evaluation 1.7.2016 - 21.12.2016" AS eval_2
    FROM EMPLOYEE_SALARY_EVALS
)
UNPIVOT (
    (salary_delta, personal_evaluation_delta)
    FOR period IN (
        (sal_1, eval_1) AS ('2016-01-01,2016-06-30'),
        (sal_2, eval_2) AS ('2016-07-01,2016-12-21')
    )
)
UNPIVOT INCLUDE NULLS

This achieves the same result but is cleaner if you have more periods to add later.

Final Notes: #

• If you need to create a permanent table with this structure, wrap the query in CREATE TABLE target_table_name AS SELECT ....
• Double-check that your date formats match SAP HANA's expectations (using TO_DATE() avoids string-to-date conversion issues).

内容的提问来源于stack exchange，提问作者Dominik Novotný