SQL Group up rows data into one row

Question

I have data in a table like this:

I want to organise the table data so that I can get a maximum of 3 letters per row grouped by account number.

Below would be the result I want:

I can use dense rank to group up the account numbers but not sure how to get the data I want in the format above.

Logic: There are 4 letters for account 123. Final result groups by account number with first 3 letters as you can only have a maximum of 3 letters per row. The fourth letter must go on the second row.

Answer 1

Here's one option using conditional aggregation, first creating a row_number, and then creating a row grouping using every 3 rows with % (modulus operator):

select account_number, 
       max(case when rn % 3 = 1 then letter end) as letter1,
       max(case when rn % 3 = 2 then letter end) as letter2,
       max(case when rn % 3 = 0 then letter end) as letter3
from (
    select *, row_number() over (partition by account_number, rn % 3 order by rn) newrn
    from (
       select *, row_number() over (partition by account_number order by letter) rn
        from yourtable 
        ) t
    ) y
group by account_number, newrn
order by account_number

• Online Demo