You can translate the question and the replies:

Dynamic column names generated from a pivot

Is there a way in Denodo to do a pivot where you generate the column names from a column's distinct values. The only example I could find for Denodo used static values you already knew. I am assuming if there is, it is going to have to be custom VQL query?
user
28-05-2015 09:49:29 -0400

8 Answers

Hi, I would suggest you to create a custom wrapper or a stored procedure to have dynamic values for column names in creating a pivot view. For example, in a custom wrapper you can do a distinct SELECT, iterate over the values and use them as the column names for the VQL query. You can check out the 'Developing Custom Wrappers' and 'Developing Stored Procedures' sections in the 'Virtual DataPort Developer Guide' to know more about creating custom wrappers and stored procedures. Hope this helps.
Denodo Team
29-05-2015 08:49:51 -0400
That is exactly what I was looking for, thanks!
user
29-05-2015 09:01:09 -0400
This question is about 5yrs old so thought I'd ask; If we asked the same question now, is building a custom stored procedure still the appropriate answer? We were looking for the ability to dynamically pivot our data but didn't look like this was natively supported. Thanks!
user
16-04-2020 14:55:24 -0400
Hi, I would prefer to develop a custom stored procedure or wrapper to implement the dynamic column generation from Pivot function. In order to apply the Pivot or Unpivot functionality in Virtual DataPort, I would follow the steps provided in [How to Pivot and Unpivot views]( https://community.denodo.com/kb/view/document/How%20to%20Pivot%20and%20Unpivot%20views?category=Combining+Data) knowledge base article. Hope this helps
Denodo Team
24-04-2020 02:20:09 -0400
Hi, Can soemone list the steps in details on how this was achieved?
user
21-06-2021 18:40:59 -0400
Hi, I would follow the below steps to develop a stored procedure to implement pivot or unpivot functionality: * [Develop a custom stored procedure]() using the [Denodo4E plugin](https://community.denodo.com/docs/html/browse/latest/en/denodo4e/index#denodo4eclipse-plugin-guide) written in JAVA and implement it in a jar file. In this stored procedure, you could invoke the methods provided in [Virtual DataPort API Javadoc]( https://community.denodo.com/docs/html/browse/8.0/en/vdp/javadoc/index) which would help you to implement the functionality of Pivot or Unpivot in Virtual DataPort views. For example, you could invoke the method **getColumns()** to obtain the columns of a view. Check the sample stored procedures in the directory <DENODO_HOME>/samples/vdp/storedProcedures. * Import the jar file of the stored procedure into Virtual DataPort by navigate to *File > Extension Management* available in Virtual DataPort Administration tool. You could refer to the section [Importing Stored Procedures](https://community.denodo.com/docs/html/browse/8.0/en/vdp/administration/stored_procedures/importing_stored_procedures/importing_stored_procedures#importing-stored-procedures) for more information. * In Virtual DataPort Administration, click the menu *File > New > Stored procedure* and select the jar file that contains the stored procedure. Now, you could provide the required input values to execute this stored procedure. Refer to the section [Executing Stored Procedures](https://community.denodo.com/docs/html/browse/8.0/en/vdp/administration/stored_procedures/executing_stored_procedures/executing_stored_procedures#executing-stored-procedures) for more information on it. Hope this helps
Denodo Team
30-06-2021 08:03:05 -0400
Can you give me an example of the stored procedure to unpivot the columns? From this: ID Date 1 Date 2 Date 3 Date 4 Date 84 ID1 ID2 ID3 ID4 To this: ID1 Date 1 ID1 Date 2 ID1 Date 3 ID1 Date 84 ID2 Date 1 ID2 Date 2 ID2 Date 3 ID2 Date 84 Thanks!
user
24-09-2021 14:13:12 -0400
Hi I would create a [Custom stored procedure](https://community.denodo.com/docs/html/browse/8.0/en/vdp/developer/developing_extensions/developing_stored_procedures/developing_stored_procedures#developing-stored-procedures) in JAVA using the Denodo4e plugin by implementing the functionality of Unpivot in Virtual DataPort views. You could refer to the sample stored procedures in the directory <DENODO_HOME>/samples/vdp/storedProcedures. Hope this helps!
Denodo Team
08-10-2021 06:19:33 -0400
You must sign in to add an answer. If you do not have an account, you can register here