Pivoting transforms rows and columns in a multidimensional data model, making data presentation more intuitive and easy to analyze.
The following MDX statement queries the turnover of Boeing 747 aircraft over the years, with the date year information displayed in the row position and the aircraft model displayed in the column position.
select
{[Date].[ALL].[2020], [Date].[ALL].[2021], [Date].[ALL].[2022]} on ROWS,
{
[Aircraft Type].[ALL].Boeing.[Boeing 747]
} on COLUMNS
from [Airline Turnover];
| Boeing 747 | |
| 2020 | 514564410 |
| 2021 | 604367094 |
| 2022 | 705093902 |
If you want to rotate the above query, so that the year information of the date dimension is displayed in the column position, and the aircraft type is in the row position, just swap the positions of the COLUMNS and ROWS keywords.
select
{[Date].[ALL].[2020], [Date].[ALL].[2021], [Date].[ALL].[2022]} on COLUMNS,
{
[Aircraft Type].[ALL].Boeing.[Boeing 747]
} on ROWS
from [Airline Turnover];
| 2020 | 2021 | 2022 | |
| Boeing 747 | 514564410 | 604367094 | 705093902 |