You can translate the question and the replies:

How to convert rows to columns using SPLIT function/any generic function in Denodo

Hi Team, I need to know is there any generic function available in DENODO for SPLIT function which can be used for any of the views without depending on one single view/function. Example: In Oracle we do have below function which can be used in any of the columns in where class. (SELECT trim(regexp_substr(:Value, '[^,]+', 1, LEVEL)) str FROM DUAL CONNECT BY instr(:Value, ',', 1, LEVEL - 1) > 0) In SAP HANA: SELECT "OUTPUT_SPLIT" FROM "_SYS_BIC"."TMP::TF_SPLIT_STRING" (?) Thanks in Advance!
#SQL #Denodo #rowsToColumns #Split
user
05-11-2020 04:22:00 -0500
code

1 Answer

Hi, I was able to split the column values based on a given regular expression into multiple rows using the [**SPLIT**](https://community.denodo.com/docs/html/browse/8.0/vdp/vql/appendix/syntax_of_condition_functions/text_processing_functions#split) text processing function of Denodo Platform. Since this function generates an array field, you can also use the [**Flatten**](https://community.denodo.com/docs/html/browse/latest/vdp/vql/queries_select_statement/from_clause/flatten_view_flattening_data_structures#syntax-of-the-flatten-operation) operation for obtaining the values inside the array field along with the [**WITH**](https://community.denodo.com/docs/html/browse/latest/vdp/vql/queries_select_statement/with_clause/with_clause#with-clause) clause. You can have a look at the [**Text Processing Functions**](https://community.denodo.com/docs/html/browse/8.0/vdp/vql/appendix/syntax_of_condition_functions/text_processing_functions#text-processing-functions) section of Virtual DataPort VQL Guide for more information. Hope this helps!
Denodo Team
06-11-2020 04:45:33 -0500
code
You must sign in to add an answer. If you do not have an account, you can register here