Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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

asked 23 Apr, 04:53

Baron's gravatar image

Baron
2.2k139151180
accept rate: 48%

edited 23 Apr, 04:54

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;

(23 Apr, 05:14) Baron
Replies hidden
1

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

(23 Apr, 05:17) Volker Barth

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.

(23 Apr, 05:49) Baron

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;
permanent link

answered 23 Apr, 05:32

Volker%20Barth's gravatar image

Volker Barth
40.3k363552824
accept rate: 34%

edited 23 Apr, 05:35

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×14

question asked: 23 Apr, 04:53

question was seen: 277 times

last updated: 23 Apr, 05:49