How can I get from this table :

To this table automatically ? :

By automatic I'm referring to that fact that I don't want to refer to the table content because this is a simple example and there much more values I have to deal with !

Thank you very much :)

CodePudding user response：

You can try to use Row_number window function with aggregate condition function.

SELECT key,
       max(case when header1 = 'A' then date end) first_date_A,
       max(case when header1 = 'B' then date end) first_date_B
FROM (
 SELECT *,Row_number() over(partition by header1,key order by date) rn
 FROM t1
) tt
WHERE rn = 1
GROUP BY key

CodePudding user response：

D-Shih's answer can be written a little more Snowflaky:

SELECT 
    key,
    max(iff(header1 = 'A', date, null)) AS first_date_A,
    max(iff(header1 = 'B', date, null)) AS first_date_B
FROM (
    SELECT *
    FROM fake_data
    QUALIFY Row_number() over(partition by header1, key order by date) = 1
) tt
GROUP BY key

which with the given data:

WITH fake_data AS (
    SELECT * FROM VALUES
    ('xxxx','A','C',1),
    ('xxxx','A','D',2),
    ('xxxx','B','C',3),
    ('xxxx','B','D',4)
    t(key, header1, header2, date)
)

and works for the A/B values because of the ROW_NUMBER, but does not give you the C/D values, and given you mention "it a lot harder" but didn't mention that column in your wanted output, I will assume you want that also.

SELECT DISTINCT
    key
    ,first_value(iff(header1 = 'A', date, null)) ignore nulls over (partition by key order by date) as first_date_a
    ,first_value(iff(header1 = 'B', date, null)) ignore nulls over (partition by key order by date) as first_date_b
    ,first_value(iff(header2 = 'C', date, null)) ignore nulls over (partition by key order by date) as first_date_c
    ,first_value(iff(header2 = 'D', date, null)) ignore nulls over (partition by key order by date) as first_date_d
FROM fake_data;

gives: