pivot_demo.sql

create extension if not exists tablefunc;

create schema demo;

create table demo.main_table
(
    the_key   text not null,
    the_pivot text not null,
    the_data  text not null,
    constraint main_pkey primary key (the_key, the_pivot)
);

create table demo.other_data
(
    other_key text primary key,
    other_value text not null
);

insert into demo.main_table values
('key 1', 'pivot 1',   'k1p1'),
('key 1', 'pivot 2',   'k1p2'),
('key 1', 'pivot 3', 'k1p3'),
('key 2', 'pivot 1',   'k2p1'),
('key 2', 'pivot 3', 'k2p3'),
('key 3', 'pivot 3',  'k3p2');

insert into demo.other_data values
('key 1', 'other one'),
('key 2', 'other two');

TestPivoting.java

package a.test;

import static db.demo.Tables.MAIN_TABLE;
import static db.demo.Tables.OTHER_DATA;

import java.util.ArrayList;
import java.util.List;

import org.jooq.DSLContext;
import org.jooq.Field;
import org.jooq.Record;
import org.jooq.Result;
import org.jooq.ResultQuery;
import org.jooq.SQLDialect;
import org.jooq.Select;
import org.jooq.conf.ParamType;
import org.jooq.impl.DSL;

public class TestPivoting
{
    public static DSLContext dsl = DSL.using(SQLDialect.POSTGRES);

    //The basic values that we're going to pivot on
    public static Select rawValues =         
                    dsl.select
                    (
                        MAIN_TABLE.THE_KEY,
                        MAIN_TABLE.THE_PIVOT,
                        MAIN_TABLE.THE_DATA
                    ).from(MAIN_TABLE);
 
    //The extra columns that will appear in the pivot table
    public static Select pivotColumns = 
                    dsl.selectDistinct(MAIN_TABLE.THE_PIVOT)
                    .from(MAIN_TABLE)
                    .orderBy(MAIN_TABLE.THE_PIVOT);
    
    public static Select otherRawValues =
                    dsl.select
                    (
                        OTHER_DATA.OTHER_KEY,
                        DSL.val("pivot 4").as(MAIN_TABLE.THE_PIVOT.getName()),
                        OTHER_DATA.OTHER_VALUE
                    ).from(OTHER_DATA);
    
    //Why doesn't this work without the "from". I get a database error that "dual" doesn't exist
    public static Select otherPivotColumns = 
                    dsl.selectDistinct(DSL.val("pivot 4"))
                    .from(OTHER_DATA);
    
    public static void demonstrate(DSLContext context)
    {
        //This is the "simple case", where you take values and just pivot them
        System.out.println(pivot(context, rawValues, pivotColumns));
        //Which yeilds
        /*
            +-------+-------+-------+-------+
            |the_key|pivot 1|pivot 2|pivot 3|
            +-------+-------+-------+-------+
            |key 1  |k1p1   |k1p2   |k1p3   |
            |key 2  |k2p1   |{null} |k2p3   |
            |key 3  |{null} |{null} |k3p2   |
            +-------+-------+-------+-------+
        */
        
        //If there's another set of values, it is always possible to generate 
        // an extended pivot by unioning the other values and their pivots 
        // as follows.
        //The problem is that it is pretty horrible to set up in the generaal case, 
        // but it gives the right answer
        System.out.println
        (
            pivot
            (
                context, 
                DSL.select().from(rawValues.union(otherRawValues)).orderBy(1, 2), 
                DSL.select().from(pivotColumns.union(otherPivotColumns)).orderBy(1)
            )
        );
        //Which yeilds
        /*
        +-------+-------+-------+-------+---------+
        |the_key|pivot 1|pivot 2|pivot 3|pivot 4  |
        +-------+-------+-------+-------+---------+
        |key 1  |k1p1   |k1p2   |k1p3   |other one|
        |key 2  |k2p1   |{null} |k2p3   |other two|
        |key 3  |{null} |{null} |k3p2   |{null}   |
        +-------+-------+-------+-------+---------+
        */
        
        
        //But what I'd really like to do is something along the lines of
        ResultQuery<Record> thePivotQuery = pivotQuery(context, rawValues, pivotColumns);
        
        //And then join that against OTHER_VALUES
        
//        thePivotQuery.join(OTHER_DATA)
//            .on(OTHER_DATA.OTHER_KEY.eq(thePivotQuery.getField(0)));
        
        //which doesn't actually compile, and I can't quite work out what to do....
    }
    
    
    public static ResultQuery<Record> pivotQuery(DSLContext context, Select<?> raw, Select<?> crossTabValues)
    {
        List<Field<?>> resultFields = new ArrayList<Field<?>>();
        
        //The result will contain all but the last two columns for the raw fields.
        Field<?> []rawFields = raw.fields();
        for (int i = 0; i < rawFields.length - 2; i++)
        {
            resultFields.add(rawFields[i]);
        }
        
        //And then one column for each cross tab value specified
        Result<?> crossTabHeadings = context.fetch(crossTabValues);
        for (Record r : crossTabHeadings)
        {
            resultFields.add
            (
                DSL.field
                (
                    r.getValue(0).toString(), 
                    rawFields[rawFields.length - 1].getDataType(context.configuration())
                )
            );
        }

        //And postgres requires that the names of the resultant fields be specified
        // explicitly, using 'ct', <"Name of Field" type> pairs...
        StringBuffer ctList = new StringBuffer();
        for (int i = 0; i < resultFields.size(); i++)
        {
            ctList.append
            (
                "\"" + resultFields.get(i).getName() + "\" " 
                     + resultFields.get(i).getDataType(context.configuration())
                                          .getTypeName(context.configuration())
            );
            
            if (i < resultFields.size() - 1)
            {
                ctList.append(", ");
            }
        }
        
        return dsl.resultQuery
        (
            "select * from crosstab('"
                + raw.getSQL(ParamType.INLINED).replace("'", "''") + "', '"
                + crossTabValues.getSQL(ParamType.INLINED).replace("'", "''") + "') "
                + "as ct(" + ctList.toString() + ")"
        );
    }
    
    public static Result<Record> pivot(DSLContext context, Select<?> raw, Select<?> crossTabValues)
    {
        ResultQuery<Record> pivot = pivotQuery(context, raw, crossTabValues);
        pivot.attach(context.configuration());
        return pivot.fetch();
    }
}