Unpivot functionality

Moderator: NorbertKrupa

Post Reply
rajasekhart
Intermediate
Intermediate
Posts: 87
Joined: Wed Apr 25, 2012 8:10 am

Unpivot functionality

Post by rajasekhart » Thu Oct 03, 2013 1:55 pm

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!
Rajasekhar.T|HP ATP Vertica Big Data Solutions V1

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1804
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Unpivot functionality

Post by JimKnicely » Thu Oct 03, 2013 2:09 pm

Hi,

Check out this post. It should get you started :)

viewtopic.php?f=63&t=831
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

Re: Unpivot functionality

Post by nnani » Thu Oct 03, 2013 3:54 pm

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.. :)
nnani........
Long way to go

You can check out my blogs at vertica-howto

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1804
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Unpivot functionality

Post by JimKnicely » Thu Oct 03, 2013 5:09 pm

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 :!:
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Unpivot functionality

Post by id10t » Thu Oct 03, 2013 8:21 pm

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.

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)
Here I inserted a very long name ~65000 length long, and result: unhandled Exception.

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,...
Use in Group_Concat (almost same functionality and it isn't so hard to modify Group_Concat that will act as Transpose).

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)
PSNever, but never don't use in UDF that you didn't tested.

Post Reply

Return to “Vertica Database Development”