UNPIVOT functionality

sKwasKwa Registered User

Hi all!

I created a template/example of UNPIVOT UDFx on C++ for this question on StackOverflow.

Source code here.

Supported data types:

  • boolean
  • all numbers
  • char/varchar/long varchar

Example

Data

 => select * from T;
 BOOL | INT | FLOAT  |  NUM   | CHAR | VCHAR |    DATE
------+-----+--------+--------+------+-------+------------
 t    |  42 | 3.1415 | 9.9900 | @    | text  | 2018-01-01
(1 row)

Unpivot

=> select unpivot(*) over () from T;
  KEY  |         VALUE
-------+-----------------------
 BOOL  | true
 INT   | 42
 FLOAT | 3.1415
 NUM   | 9.9900
 CHAR  | @
 VCHAR | text
 DATE  | UNSUPPORTED DATA TYPE
(7 rows)

=> select unpivot(T.int, T.vchar) over () from T;
  KEY  | VALUE
-------+-------
 INT   | 42
 VCHAR | text
(2 rows)

Comments

  • Ben_VandiverBen_Vandiver Employee, Registered User, VerticaExpert

    Looks nice! Two suggestions:
    1) I'd provide for an 'id' column - you can specify a column (or columns) not to be unpivoted.
    2) I'd look to pre-allocate the buffer for the ostringstream so you don't do allocation inside the loop. Probably would improve performance in a measurable way.

  • sKwasKwa Registered User

    Hi !

    @Ben_Vandiver
    Thanks for feedback. This function I will hone to shine. I have a couple of ideas for how to improve performance. Also I'm going to add support for all types of data.

    I'd provide for an 'id' column - you can specify a column (or columns) not to be unpivoted.

    Added to the TODO list. Thanks, good idea.

    I'd look to pre-allocate the buffer for the ostringstream.

    Sure. Also I improve data type conversion(I don't like too much if-else blocks inside loop).

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file