The Pivot Column option helps you restructure your data for better analysis and easier comparisons. Pivoting data essentially means converting row values from one column into multiple columns. To demonstrate pivoting data, let's take the region-wise sales for home appliances as an example.
Sample data
We've pivoted the tabular data above based on the PRODUCT column. Notice how the product column has been converted into multiple columns - Microwave, Refrigerator, and Coffee Machines.
Pivoted data based on the product field
Let's see how we can pivot columns in Infobridge. The sample table contains country, product, and market data. We'll pivot the "Product" dimension.
Sample query to pivot data
Select "Product" from the Category dropdown. We'll leave default options for the Operations and Values fields unchanged.
Pivot column configuration
Notice how each product is now a separate column.
Pivoted column based on the product dimension
We can choose different aggregation types like average, minimum, or maximum when we pivot a column as shown below
Other aggregation types
Unpivot column
Unpivot does the opposite of pivot - it converts multiple column dimensions into a single row dimension. Let's consider the Product example - the sales for each product is captured in a separate column.
Sales for each product is in a separate column
Click on the Unpivot option from the Transform ribbon. Select the columns to be unpivoted into rows from the Column dropdown. We've selected all the product columns.
Unpivot columns
When you click Apply, each product column is converted into a single column titled "Attribute" that contains all the products as multiple rows. The sales for each product is also converted into a single column titled "Value" with multiple rows corresponding to each product.
Unpivoted data for the product columns
Finally let's use the Rename Column button to change the name of the default "Attribute" and "Value" columns to relevant, meaningful names.
