combining pivot and window

[Baron]

I can't solve this "simple" problem, can maybe someone help?

What I have is:

create or replace table mytable (whichamount int, whichdate date)
insert into mytable values
(100, '2024-01-01'),
(200, '2024-01-01'),
(300, '2024-02-01'),
(400, '2024-03-01'),
(500, '2024-04-01'),
(600, '2024-04-01');

What I want is:

whichdate, whichamount1, whichamount2
'2024-01-01', 100, 200
'2024-02-01', 300, null
'2024-03-01', 400, null
'2024-04-01', 500, 600

[Baron]

This is one solution from AI:

SELECT 
    whichdate,
    MAX(CASE WHEN rn = 1 THEN whichamount END) AS whichamount1,
    MAX(CASE WHEN rn = 2 THEN whichamount END) AS whichamount2
FROM (
    SELECT 
        whichdate,
        whichamount,
        ROW_NUMBER() OVER (PARTITION BY whichdate ORDER BY whichamount) AS rn
    FROM mytable
) t
GROUP BY whichdate
ORDER BY whichdate;

[VolkerBarth]

What does the AI tell when there are more than 2 entries for the same "whichdate"?

[Baron]

you have right, and I see the difference in your answer. But in my question to AI I have told him that I am expecting 2 columns.

[VolkerBarth · Accepted solution]

Here's a similar take with PIVOT:

SELECT *
FROM ( SELECT 
        whichdate,
        whichamount,
        ROW_NUMBER() OVER (PARTITION BY whichdate ORDER BY whichamount) AS rn
    FROM mytable
     ) PivotSourceData
   PIVOT ( 
      LIST (whichamount)
      FOR rn IN ( 1 as whichamount1, 2 as whichamount2)
   ) PivotedData
ORDER BY whichdate;