How to group by row in diferents columns

I have this table in SQL, I need to group it by the report name and I need to split the price_bucket column and the count_sales column in diferents rows.

+-----------+-------------+--------------+--------------------+-------------+--+
| report_id | report_name | order_number | price_bucket_range | count_sales |  |
+-----------+-------------+--------------+--------------------+-------------+--+
|        69 | London      |            1 | 0 - 1              |           4 |  |
|        69 | London      |            2 | 1 - 2              |           8 |  |
|        69 | London      |            3 | 2 - 3              |           2 |  |
|        69 | London      |            4 | 3 - 4              |           2 |  |
|        69 | London      |            5 | 4 - 1000           |           0 |  |
|        70 | Berlin      |            1 | 0 - 1              |          11 |  |
|        70 | Berlin      |            2 | 1 - 2              |           3 |  |
|        70 | Berlin      |            3 | 2 - 3              |           2 |  |
|        70 | Berlin      |            4 | 3 - 4              |           1 |  |
|        70 | Berlin      |            5 | 4 - 1000           |           1 |  |
|        71 | Paris       |            1 | 0 - 2              |           5 |  |
|        71 | Paris       |            2 | 2 - 3              |          17 |  |
|        71 | Paris       |            3 | 3 - 5              |           6 |  |
|        71 | Paris       |            4 | 5 - 10             |           0 |  |
|        71 | Paris       |            5 | 10 - 1000          |           1 |  |
+-----------+-------------+--------------+--------------------+-------------+--+

I think that I should use the PIVOT funtion in SQL Server and an intermediate table. How should I do it?

This is what the final table should look like:

| report_id | report_name | price_bucket_1 | count_sales_1 | price_bucket_2 | count_sales_2 | price_bucket_3 | count_sales_3 | price_bucket_4 | count_sales_4 | price_bucket_5 | count_sales_5 |
+-----------+-------------+----------------+---------------+----------------+---------------+----------------+---------------+----------------+---------------+----------------+---------------+
|        69 | London      | 0 - 1          |             4 | 1 - 2          |             8 | 2 - 3          |             2 | 3 - 4          |             2 | 4 - 1000       |             0 |
|        70 | Berlin      | 0 - 1          |            11 | 1 - 2          |             3 | 2 - 3          |             2 | 3 - 4          |             1 | 4 - 1000       |             1 |
|        71 | Paris       | 0 - 2          |             5 | 2 - 3          |            17 | 3 - 5          |             6 | 5 - 10         |             0 | 10 - 1000      |             1 |
+-----------+-------------+----------------+---------------+----------------+---------------+----------------+---------------+----------------+---------------+----------------+---------------+

More info: The order_number column sometimes will have 5 rows and sometimes will have 4. In that case the last 2 columns must have 0. But never more than 5 rows.

Thank you so much for the help.

Answer

If I understand correctly, you need to list the buckets in order by the sales descending. You can use row_number() and conditional aggregation:

select report_id, report_name,
       max(case when seqnum = 1 then price_bucket_range end) as bucket_1,
       max(case when seqnum = 1 then count_sales end) as count_1,
       max(case when seqnum = 2 then price_bucket_range end) as bucket_2,
       max(case when seqnum = 2 then count_sales end) as count_2,
       max(case when seqnum = 3 then price_bucket_range end) as bucket_3,
       max(case when seqnum = 3 then count_sales end) as count_3,
       max(case when seqnum = 4 then price_bucket_range end) as bucket_4,
       max(case when seqnum = 4 then count_sales end) as count_1,
       max(case when seqnum = 5 then price_bucket_range end) as bucket_5,
       max(case when seqnum = 5 then count_sales end) as count_5
from (select t.*,
             row_number() over (partition by report_id order by count_sales desc) as seqnum
      from t
     ) t
group by report_id, report_name;