You can translate the question and the replies:

Dynamic column names for Pivot

Hi Team, I have a requriement where in i need to PIVOT rows in to column where the column name is not static. While reading I could find that theres a solution to achieve this. Can someone please give me a detail step by step solution for this? 1. 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. 2. https://community.denodo.com/answers/question/details?questionId=90670000000CdG6AAK&titleDynamic%20column%20names%20generated%20from%20a%20pivot How to achieve the above solution? Any help would be appreciated!! Thanks!!
user
21-06-2021 18:59:36 -0400

1 Answer

Hi, In order to create a Store Procedure that would set the column name of a Pivot view dynamically I would perform the following: * In the Java class for the Custom Stored Procedure, I would first execute a query to get the distinct rows of the specific column from the view that is turned into seperate columns in the Pivot view. I would use the following syntax to get the distinct rows in a column ``` SELECT distinct(<KEY_COLUMN_NANME>) FROM <VIEW> ``` * I would then pass the array of these distinct values to a **CREATE VIEW** query, such that when the Stored Procedure is imported and executed using the Virtual DataPort Administration Tool, the column names get created dynamically. You can take a look at tutorial [How to implement a Stored Procedure ](https://community.denodo.com/tutorials/browse/customcomponents/3storedprocedure) for more step by step instruction on how to create a custom Stored Procedure. You can also refer to the Knowledge Base article[ How to Pivot and Unpivot views](https://community.denodo.com/kb/en/view/document/How%20to%20Pivot%20and%20Unpivot%20views) for more information on creating Pivot views If you still need help and you are valid support user, then you can raise a new Support case at the[ Denodo Support Site](https://support.denodo.com/) so that our Support Team will assist you. Hope this helps!
Denodo Team
22-06-2021 08:53:04 -0400
You must sign in to add an answer. If you do not have an account, you can register here