The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
Need to split multiple column which contains pipeline seperated data in to multiple rows
Hello,
My data is stored in the below format:
ID VALUE_1 VALUE_2
1|2 20|30 40|50
Expected format:
ID VALUE_1 VALUE_2
1 20 40
2 30 50
Can anyone help me on this as i am stucked with the above.
Thanks,
Suhrid Ghosh
0
Answers
This might be easiest to solve with a pre-processor, either an external script or a UDx that explodes each row.
Current Vertica supports ARRAY type, so you could load data like this, but need to edit array and record delimiters:
dbadmin=> create table array3 (a1 array[int], a2 array[int], a3 array[int]);
dbadmin=> copy array3 from stdin;
1,2|20,30|40,50
dbadmin=> select * from array3;
a1 | a2 | a3
-------+---------+---------
[1,2] | [20,30] | [40,50]
Then use array functions like position access and EXPLODE to expand each row.
@Suhrid_R__Ghosh - Here is a an example per @Bryan_H 's great suggestion!
If you don't have Vertica 10, here's another possibility:
Keep it simple ... we have
SPLIT_PART()
in Vertica ...