Page 1 of 1

Row-Column transpose pivotted on one column

Posted: Wed May 20, 2015 10:53 am
by Abhijat Chowdhary
Hi,

Say we have the following table test

+-----------+-------+--------+------------+
| Customers | Units | Orders | Month_Name |
+-----------+-------+--------+------------+
| 1000 | 1200 | 1100 | April |
| 2000 | 2400 | 2200 | May |
+-----------+-------+--------+------------+

I want to perform a row-column transpose on this table pivotted on the column 'Month_Name' such that the resulting data set looks like this:

+-------------+-------+------+
| Metric_Name | April | May |
+-------------+-------+------+
| Customers | 1000 | 2000 |
| Units | 1200 | 2400 |
| Orders | 1100 | 2200 |
+-------------+-------+------+

After going through some of the options already provided I came with this solution using case statements and multiple unions, which seems a bit brute force.

SELECT 'Customers' as Metric_Name , Max(CASE WHEN Month_Name = 'April' THEN Customers END) April,
Max(CASE WHEN Month_Name = 'May' THEN Customers END) May
FROM test UNION SELECT 'Units' as Measure , Max(CASE WHEN Month_Name = 'April' THEN Units END) April,
Max(CASE WHEN Month_Name = 'May' THEN Units END) May
FROM test UNION SELECT 'Orders' as Measure , Max(CASE WHEN Month_Name = 'April' THEN Orders END) April,
Max(CASE WHEN Month_Name = 'May' THEN Orders END) May
FROM test;

Is there a better way to perform the get the above result? Are there any in-built vertica functions to help do the same?

Also, in the above example I am working with static column names ('April','May'). How to handle the case where the column values are not known prior to executing the query and need to be figured out dynamically?