Nested pivot columns

Question

I have a table sales:

SalesID. Date. Geography

1.       2020-01.  Italy
1.       2020-01.  France
1.       2020-01.  UK
2.       2020-02.  Italy
2.       2002-02.  Canada
3.       2002-08.  France

I would like to display only top 2 Geography and pivot:

Expected result:

SalesID.   Date. Geography 1.    Geography2.
1.      2020-01. Italy. France

2.     2020-02.  Italy.  France.
3.    2020-08.    France

What I try so far https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=565f80f146fe2a72096b31fc0baba3ad

How to modify it?

score 0 · Answer 1 · answered Dec 21 '20 at 11:30

First, you need a column that defines the ordering of the rows - I assumed ordering_id. Then, you can use row_number() and conditional aggregation.

select saleid, date,
    max(case when rn = 1 then geography end) as geography1,
    max(case when rn = 2 then geography end) as geography2
from (
    select t.*,
        row_number() over(partition by saleid, date order by ordering_id) as rn
    from mytable t
) t
where rn <= 2
group by saleid, date

score 0 · Accepted Answer · answered Dec 21 '20 at 11:30

You can use the conditional aggregation as follows:

select t.sales_id,
       date, 
       max(CASE WHEN rn = 1 THEN geography END) AS geography1,
       max(CASE WHEN rn = 2 THEN geography END) AS geography2
(select t.*, row_number() over (partition by t.sales_id, date order by t.date) as rn
FROM your_table t) t
GROUP BY t.sales_id, date;

Nested pivot columns

2 Answers2