How do I do a Crosstab in Vertica

Question

What is the Vertica equivalent of crosstab or pivot table found in PostgreSQL and other RDBMS's?

Answer 1

Vertica doesn't currently (release 3.5.9 for future reference) have that capability. I talked with a co-worker and his suggestion was to play around with "cross join."

The other option would be to distill your results as far as you could and then run a process outside of the database (perl, Java, C#, etc) that manipulated the data.

Vertica is open to feature requests however.

I don't believe this will be available in the upcoming 4.0 release either.

Answer 2

Vertica can do a cross-tab using the basic sql-92 syntax:

select 
   DIM1
   ,DIM2
   ,DIM3
   ,SUM(MEASURE1)
   ,SUM(MEASURE2)
   ,SUM(MEASURE3)
from  
 (select 
   DIM1
   ,DIM2
   ,DIM3
   ,MAX(CASE WHEN MEASURE = 'MEASURE1' then MEASURE1_VALUE else null end) MEASURE1
   ,MAX(CASE WHEN MEASURE = 'MEASURE2' then MEASURE2_VALUE else null end) MEASURE2
   ,MAX(CASE WHEN MEASURE = 'MEASURE3' then MEASURE3_VALUE else null end) MEASURE3
from SOME_TABLE
group by DIM1, DIM2, DIM3
) as a
group by DIM1, DIM2, DIM3