|
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 |
|
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;
|
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;What does the AI tell when there are more than 2 entries for the same "whichdate"?
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.