MySQL self join but in the same row

user_16692902 0
Viewed 37

I have this table:

date sku note
11/12 123 note
11/13 123 note
11/12 456 note
11/13 456 note
11/12 789 note
11/13 789 note

(Note is random) And I want the result to return this:

date sku note sku note sku note
11/12 123 note 456 note 789 note
11/13 123 note 456 note 789 note

I tried self join

select * from schedules a, schedules b 
where a.date = b.date;

but this is what I got:

date sku note sku
11/12 123 note 456
11/12 123 note 789
11/13 123 note 456
11/13 123 note 789
mysql sql
1 Answers

Calculate a row_number, then pivot.

select `date`
, max(case when rn = 1 then sku end) as sku1
, max(case when rn = 1 then note end) as note1
, max(case when rn = 2 then sku end) as sku2
, max(case when rn = 2 then note end) as note2
, max(case when rn = 3 then sku end) as sku3
, max(case when rn = 3 then note end) as note3
from
(
    select `date`, sku, note
    , row_number() over (partition by `date` order by sku) as rn
    from schedules 
) q
where rn <= 3
group by `date`
order by `date`
user_40034190
Related