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?
Row-Column transpose pivotted on one column
Moderator: NorbertKrupa
-
- Newbie
- Posts: 1
- Joined: Wed May 20, 2015 10:25 am
Row-Column transpose pivotted on one column
Post by Abhijat Chowdhary » Wed May 20, 2015 10:53 am
Return to “Vertica Tips, Lessons and Examples”
Jump to
- General
- ↳ Welcome to vertica-forums.com!
- ↳ Board Announcements
- ↳ Request for New Forums Categories
- ↳ Announcements, Events and Activities
- ↳ Vertica Links
- ↳ General
- ↳ Employment
- ↳ Vertica Certification
- Newbie
- ↳ New to Vertica
- ↳ New to Vertica Database Administration
- ↳ New to Vertica Database Development
- ↳ New to Vertica SQL
- Using Vertica
- ↳ Vertica Database Administration
- ↳ Vertica Database Development
- ↳ Vertica SQL
- ↳ Vertica SQL Functions
- ↳ Vertica Database Designer (DBD)
- ↳ Vertica User Defined Functions (UDFs)
- ↳ Vertica External Procedures
- ↳ Vertica Analytics
- ↳ Vertica Management Console
- ↳ Vertica Error Codes
- ↳ Vertica Backup & Recovery
- ↳ Vertica Installation
- ↳ Vertica Security
- ↳ Vertica Performance Tuning
- ↳ Vertica Administration Tools
- ↳ Vertica Upgrade
- ↳ Vertica Migration
- ↳ Vertica and the Operating System
- ↳ Vertica Data Load
- ↳ Vertica Tips, Lessons and Examples
- ↳ Vertica "How to..."
- Connecting to Vertica
- ↳ ADO.NET
- ↳ Cognos
- ↳ DBeaver
- ↳ dbVisualizer
- ↳ HDFS Connector
- ↳ Hadoop Connector
- ↳ HCatalog Connector
- ↳ Informatica PowerCenter
- ↳ JDBC
- ↳ Microsoft SQL Server Analysis Services (SSAS)
- ↳ MicroStrategy
- ↳ Microsoft SQL Server Integration Services (SSIS)
- ↳ ODBC
- ↳ Perl
- ↳ PHP
- ↳ Python
- ↳ R Language Integration
- ↳ Rhapsody Interface Engine
- ↳ SQL Workbench/J
- ↳ SQuirreL SQL Client
- ↳ Talend
- ↳ Tableau
- ↳ Toad for Data Analysts
- ↳ Vertica SDK API
- ↳ VoltDB
- ↳ vSQL
- Vertica Portfolio
- ↳ Vertica Premium Edition
- ↳ Vertica Express Edition
- ↳ Vertica Community Edition
- ↳ Vertica SQL on Hadoop
- ↳ Vertica in the AWS Cloud
- ↳ Vertica on Microsoft Azure
- All times are UTC+01:00
- Top
- Delete all board cookies
Powered by phpBB ® | phpBB3 theme by KomiDesign