Hi,
I want to turn the columns into rows.
So that i can reduce number of rows that are loaded.. (where some columns are unnecessarily redundant)
Do we have that feasibility.??
Eg:
I'm having the following structure .
Create table Unpivot(ID, Month, Year, Count1, Count2, Count3,...........Count32);
In this structure, i want that Count1 to Count32 columns should come as rows.
Thanks in advance.
Raj!
Unpivot functionality
Moderator: NorbertKrupa
-
- Intermediate
- Posts: 87
- Joined: Wed Apr 25, 2012 8:10 am
Unpivot functionality
Rajasekhar.T|HP ATP Vertica Big Data Solutions V1
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Unpivot functionality
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Unpivot functionality
Hi rajasekhart
Beside the excellent solution by knicley, Vertica also provide a UDF (User Defined Function) for this functionality on Github.
The UDF is TRANSPOSE.
Basically it does the same as you per your requirement .i.e. converts rows to columns.
Please refer to this link for using this UDF
https://github.com/vertica/Vertica-Exte ... nspose.cpp
Hope this helps..
Beside the excellent solution by knicley, Vertica also provide a UDF (User Defined Function) for this functionality on Github.
The UDF is TRANSPOSE.
Basically it does the same as you per your requirement .i.e. converts rows to columns.
Please refer to this link for using this UDF
https://github.com/vertica/Vertica-Exte ... nspose.cpp
Hope this helps..
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Unpivot functionality
Great suggestion, nnani!
It'd be interesting to see some performance tests on each of these solutions. When I get some time, I'll try each out on a large record set and post the results
It'd be interesting to see some performance tests on each of these solutions. When I get some time, I'll try each out on a large record set and post the results
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Unpivot functionality
Hi!
I don't recommend to use in Transpose UDF --- function with bugs.
For example:
1) it doesn't validates if concatenated string didn't exceed data type length
2) no validation of NULL values.
Here I inserted a very long name ~65000 length long, and result: unhandled Exception.
Use in Group_Concat (almost same functionality and it isn't so hard to modify Group_Concat that will act as Transpose).
PSNever, but never don't use in UDF that you didn't tested.
I don't recommend to use in Transpose UDF --- function with bugs.
For example:
1) it doesn't validates if concatenated string didn't exceed data type length
2) no validation of NULL values.
Code: Select all
daniel=> select * from people ;
id | name | gender
----+---------+--------
1 | Patrick | M
2 | Jim | M
3 | Sandy | F
4 | Brian | M
5 | Linda | F
6 | | F
7 | Sonya | F
8 | Anna | F
(8 rows)
Code: Select all
daniel=> select transpose(gender, name, ', ') over (partition by gender) from people;
label | group_label
-------+-----------------------------
M | Patrick, Jim, Brian
F | Sandy, Linda, , Sonya, Anna
(2 rows)
Code: Select all
daniel=> select transpose(gender, name, ', ') over (partition by gender) from people;
ERROR 3399: Failure in UDx RPC call InvokeProcessPartition(): Error calling processPartition() in User Defined Object [transpose] at [/opt/vertica/sdk/include/VerticaUDx.h:2248], error code: 0, message: User code caused Vertica to throw exception "Returned string value '[Sandy, Linda, , Anna, Sonya,...
Code: Select all
daniel=> select gender, group_concat(name) over (partition by gender) from people ;
gender | list
--------+---------------------------
M | Patrick, Jim, Brian
F | Sandy, Linda, Sonya, Anna
(2 rows)