TRANSPOSE
Pivots row values for a field to columns
TRANSPOSE(<field to transpose>, < current column name>)
select transpose(Message Type,Sent)
Transposes rows to columns based on a column Key and values based on a value column.
For example:
| Store_Id | City | Type | Count |
| 590 | Lakewood | FREE Sandwich | 1 |
| 3067 | Tacoma | Breakfast Sandwich | 1 |
| 4594 | Redmond | Everyday Deal | 1 |
When Type and Count are the parameters passed in, this is is transformed into:
| Store_Id | City | FREE Sandwich | Breakfast Sandwich | Everyday Deal |
| 590 | Lakewood | 1 | ||
| 3067 | Tacoma | 1 | ||
| 4594 | Redmond | 1 |
This does require a column to group by, if only two columns exist, the C( query to use is:
select *, 1 as dummy; select transpose(c1,c2,dummy)
You will then need to hide the dummy group by column in the widget or on the dashboard itself
To collapse based on a key field, use:
TRANSPOSE(<field to transpose>, <current column name>, <key column name>)
select transpose(Message Type, Sent, Customer)
REVERSE TRANSPOSE
Opposite of Transpose, folds columns into rows.
Syntax:
REVERSE_TRANSPOSE(<New ID column>, <New Value column>, <Value column 1>, ...., <Value column N>)(, , , ...., )
Example: http://recordit.co/8HRx7aJtmB
As you can see, I have initial data with 5 columns.
For example:
| ID1 | ID2 | V1 | V2 | V3 |
| A | lo | 1 | 2 | 3 |
| B | hi | 4 | 5 | 6 |
After executing: select reverse_transpose(NEW_ID, NEW_V, V1, V2, V3) I get:
| ID1 | ID2 | New_ID | New_V |
| A | lo | V1 | 1 |
| A | lo | V2 | 2 |
| A | lo | V3 | 3 |
| B | hi | V1 | 4 |
| B | hi | V2 | 5 |
| B | hi | V3 | 6 |
where NEW_ID's value is the old value column names (V1, V2, V3 one for each new row) and the NEW_V contains the corresponding value.
When you have multiple columns that you want to fold in, specify all the columns you want to pin it by, specify the "pin" columns at the front, followed by new ID and the value column, followed by a *.
Syntax:
REVERSE_TRANSPOSE(Customer, Campaign, State, <New ID column>, <New Value column>, *)
This will fold in all the columns except for the columns in the first section.