You can translate the question and the replies:

pivot dynamically based on values?

Is there a way to dynamically create the headers for the pivot based on the values in the data? for example if I have a field that has a mapping type that can either be "sector" or "region" and then have a value by person. and end up with a cross tab that has sector and region and new columns and the first value by user in the underlying cells?
user
12-03-2021 23:00:16 -0500

7 Answers

Hi, Denodo Platform supports Pivot and Unpivot custom functions for row and column transformations for a view. For your scenario, you could create a [**custom wrapper**](https://community.denodo.com/docs/html/browse/latest/en/vdp/developer/developing_extensions/developing_custom_wrappers/developing_custom_wrappers#developing-custom-wrappers) or [**stored procedure**](https://community.denodo.com/docs/html/browse/latest/en/vdp/developer/developing_extensions/developing_stored_procedures/developing_stored_procedures#developing-stored-procedures) to dynamically create headers based on the pivoted values of the view. You can take a look at the **[Denodo XtraFuncs - User Manual](https://community.denodo.com/docs/html/document/denodoconnects/8.0/en/Denodo%20XtraFuncs%20-%20User%20Manual#h.fcztfxfhg97q)** Knowledge Base Article for more information. Hope this helps!
Denodo Team
15-03-2021 06:31:43 -0400
the pivot section on this work instruction only shows examples for pivotregister and pivot array. I already have the values in the column I need. Are you saying I need to create an array first and then do the pivot array by column? Sorry the work instruction is not clear on my specific use case.
user
15-03-2021 07:27:44 -0400
Hi, The **[PIVOT](https://community.denodo.com/docs/html/document/denodoconnects/8.0/en/Denodo%20XtraFuncs%20-%20User%20Manual#h.fcztfxfhg97q)** function transforms the row values in an array to corresponding columns with the specified column names in the pivot definition. For your scenario, you could first create an array with the necessary columns and then use a syntax like > pivotregister(<array_name>, 'column1:type, column2:type….') This function creates a field called **"pivotregister"** which transforms the rows in the specified array into columns with the column names provided. You could then project the values of this field by right clicking on **pivotregister** field and selecting **Project subfields of pivotregister** option . Hope this helps!
Denodo Team
22-03-2021 08:44:10 -0400
Thanks -- but I do not see documentation on how to create the array in the first place based on the field data. Is there a function for this?
user
22-03-2021 08:49:00 -0400
Hi, I would use the **[NEST](https://community.denodo.com/docs/html/browse/latest/en//vdp/vql/appendix/syntax_of_condition_functions/aggregation_functions#nest)** or **[REGISTER](https://community.denodo.com/docs/html/browse/latest/en//vdp/vql/appendix/syntax_of_condition_functions/type_conversion_functions#register)** functions to create arrays in the views of the Virtual DataPort. The **NEST** is an aggregation function which will transform the list of rows as arrays using the GROUP BY operation whereas **REGISTER** is a type conversion function which will transform the list of columns into arrays without the GROUP BY operation. For your scenario, you could first use the **REGISTER** function like below for creating an array > REGISTER (field1, field2,.....fieldN) Then use the **pivotregister** function to transform the rows in the created array into columns with the column names provided like > pivotregister (<created_register_name>, 'column1:type, column2:type….columnN:type') For detailed information, you can take a look at the **[Aggregation Functions](https://community.denodo.com/docs/html/browse/latest/en//vdp/vql/appendix/syntax_of_condition_functions/aggregation_functions#aggregation-functions)** and **[Type Conversion Functions](https://community.denodo.com/docs/html/browse/latest/en//vdp/vql/appendix/syntax_of_condition_functions/type_conversion_functions#type-conversion-functions)** sections of the Virtual DataPort VQL Guide. Additionally, if you still need help and if you are a user with valid support access then you can create a support case in **[Denodo Support Site](https://support.denodo.com/)** so that our support team will assist you further. Hope this helps!
Denodo Team
29-03-2021 08:16:37 -0400
I have created an array based on 4 values that are in a concatenated field. They are in the following order in the array but do not have header information. this the content of the array for each row (field name is "value") and has an id field in the view id array <Region> <Country> <Company> <Subdivision> How can I turn these values into a table that looks like this? | id | Region| Country | Company | Subdivision | | -------- | -------- | -------- | -------- | -------- | | 1 | <region value> | < country value> | < company value> | < subdivision value> |
user
31-03-2021 09:57:59 -0400
Hi, For your scenario, I would first **[create a selection view](https://community.denodo.com/docs/html/browse/8.0/en//vdp/administration/creating_derived_views/creating_selection_views/creating_selection_views#creating-selection-views)** on top of this existing view. Then, I would create a new field called **'pivot'** and use the **[pivotregister](https://community.denodo.com/docs/html/document/denodoconnects/8.0/en/Denodo%20XtraFuncs%20-%20User%20Manual#h.fcztfxfhg97q)** function like > pivotregister(<created_array_name>, 'Region: type, Country: type, Company: type, Subdivision: type') where the **type** value is the datatype of the corresponding fields. This function creates a field called 'pivot' with an array of 4 columns such as **Region, Country, Company and Subdivision**. Then, I would create another selection view on top of this view and in the **Output** tab, Right-click on the 'pivot' field and select **'Project subfields of 'pivot''**. This will project the fields in the array into individual columns. If you still need assistance and if you are a user with valid support access then you can create a support case in **[Denodo Support Site](https://support.denodo.com/)** so that our support team will assist you further. Hope this helps!
Denodo Team
08-04-2021 02:21:52 -0400
You must sign in to add an answer. If you do not have an account, you can register here