Multiple Facets

As we have seen in this section so far, a typical cross table involves two categorical columns; one represented by the rows of the cross table and the other by the columns. Occasionally, we need to work with more than two categorical columns and wish to group by more than one categorical column across the rows or the columns of the cross table or both. We will cover how to do that in this section.

Rows

We have three grouping columns. We wish to represent two over the rows and the third over the columns of the cross table.

BEGIN
    DECLARE v_pivot_column STRING;
    SET v_pivot_column =
            (SELECT STRING_AGG(DISTINCT CONCAT('"', CAST(col_4 AS STRING), '"')),
             FROM refcon.dataset.table_1);


    EXECUTE IMMEDIATE
        FORMAT("""
                  SELECT *
                  FROM (SELECT col_10,
                               col_6,
                               col_4
                        FROM refcon.dataset.table_1) PIVOT
                           (count(1) AS count
                                        FOR CAST(col_4 AS STRING) IN (%s))
                             """, v_pivot_column);
END;

Columns

We have three grouping columns. We wish to represent two over the columns and the third over the rows of the cross table.

BEGIN
    DECLARE v_pivot_column STRING;
    SET v_pivot_column =
            (SELECT STRING_AGG(DISTINCT CONCAT('"col_6_',
                                               CAST(col_6 AS STRING),
                                               '_col_4_',
                                               CAST(col_4 AS STRING), '"')),
             FROM refcon.dataset.table_1);

    EXECUTE IMMEDIATE
        FORMAT("""
                  SELECT *
                  FROM (SELECT col_10,
                               CONCAT('col_6_', CAST(col_6 AS STRING),
                                       '_col_4_', CAST(col_4 AS STRING)) col_6_4,
                               COUNT(1)                                 value_count
                         FROM refcon.dataset.table_1
                         GROUP BY col_10, col_6_4) 
                         PIVOT (max(value_count) AS count
                                                FOR CAST(col_6_4 AS STRING) IN (%s))
                             """, v_pivot_column);
END;

Rows and Columns

We have four grouping columns. We wish to represent two over the rows and the other two over the columns of the cross table.

BEGIN
    DECLARE v_pivot_column STRING;
    SET v_pivot_column =
            (SELECT STRING_AGG(DISTINCT CONCAT('"col_6_',
                                               CAST(col_6 AS STRING),
                                               '_col_4_',
                                               CAST(col_4 AS STRING), '"')),
             FROM refcon.dataset.table_1);

    EXECUTE IMMEDIATE
        FORMAT("""
                  SELECT *
                  FROM (SELECT  col_10,
                                col_3,
                                CONCAT('col_6_', CAST(col_6 AS STRING),
                                       '_col_4_', CAST(col_4 AS STRING)) col_6_4,
                                COUNT(1)                                 value_count
                         FROM refcon.dataset.table_1
                         GROUP BY col_10,col_3, col_6_4) 
                         PIVOT (max(value_count) AS count
                                                FOR CAST(col_6_4 AS STRING) IN (%s))
                             """, v_pivot_column);
END;

Here is how this works:

This works exactly as the “Columns” scenario described above except that when we pivot, we leave behind two columns col_10 and col_3 to be represented by the rows of the cross table.

SQL
I/O