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 |
CodePudding user response：
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`