UNPIVOT functionality

sKwa

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)

Ben_Vandiver

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.

sKwa

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).