MySQL/Hive/Presto/Impala Transposition

Rows to Columns

Rows to Comma Separated String

MySQL

Use GROUP_CONCAT(). This function also works in Infobright.

GROUP_CONCAT([DISTINCT] expr [,expr …]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name …]]
[SEPARATOR str_val])

Hive

For Hive, I use collect_set() + concat_ws() from https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF.
But if you want to remove duplicated elements, write your own UDF should be the only choice till now.

Presto

Don’t know how. :(

Impala

Impala also has a group_concat() but different from mysql

group_concat(string s [, string sep])
Purpose: Returns a single string representing the argument value concatenated together for each row of the result set. If the optional separator string is specified, the separator is added between each pair of concatenated values.
Return type: string

Usage notes: concat() and concat_ws() are appropriate for concatenating the values of multiple columns within the same row, while group_concat() joins together values from different rows.

By default, returns a single string covering the whole result set. To include other columns or values in the result set, or to produce multiple concatenated strings for subsets of rows, include a GROUP BY clause in the query.

Rows to Columns

MySQL

Hive

Same as MySQL

Presto

Same as MySQL & Hive.

Impala

Still the same

Columns to Rows

Comma Separated String to Rows

UNION [ALL] SELECT seems to be a solution.

MySQL

And…A Stored Procedure or a UDF?

Hive

Lateral View is AWESOME!
I tried explode() which can split an array into rows and before that split() which split string into array.

Presto

Not figured out.

Impala

Not figured out.

Columns to Rows

UNION!

MySQL/Hive/Presto/Impala Transposition by @sskaje: https://sskaje.me/2014/02/mysql-hive-presto-impala-transposition/

Incoming search terms: