Create Columns from Distinct Values of a Column — From SQL to SPL #26

Problem description & analysis: A certain database table records the monthly sales of different products, where the values of the products are unknown. Task: Now we need to group by product and month, sum up sales amount, and then convert products from rows to columns. Code comparisons: SQL： SELECT format( $f$ SELECT * FROM crosstab( $q$ SELECT month, product, sum(amount) FROM ventas GROUP BY 1, 2 ORDER BY 1, 2 $q$ , $c$VALUES (%s)$c$ ) AS ct(month int, %s); $f$ -- end dynamic query string , string_agg(quote_literal(sub.product), '), (') , string_agg(quote_ident (sub.product), ' int, ') || ' int' ) FROM (SELECT DISTINCT product FROM ventas ORDER BY 1) sub; When SQL performs row-to-column conversion, column names must be written out, and the actual SQL must be dynamically generated using the preprocessed SQL above before executing the actual SQL, which is cumbersome. Using stored procedures can merge two steps into one, but the structure is more complex. SPL: The row-column conversion function of SPL does not need to write column names.

May 8, 2025 - 03:51

Create Columns from Distinct Values of a Column — From SQL to SPL #26

Problem description & analysis:

A certain database table records the monthly sales of different products, where the values of the products are unknown.

Task: Now we need to group by product and month, sum up sales amount, and then convert products from rows to columns.

Code comparisons:

SQL：

SELECT format(
$f$
SELECT * FROM crosstab(
   $q$
   SELECT month, product, sum(amount)
   FROM   ventas
   GROUP  BY 1, 2
   ORDER  BY 1, 2
   $q$
 , $c$VALUES (%s)$c$
   ) AS ct(month int, %s);
$f$  -- end dynamic query string
            , string_agg(quote_literal(sub.product), '), (')
            , string_agg(quote_ident  (sub.product), ' int, ') || ' int'
                 )
FROM  (SELECT DISTINCT product FROM ventas ORDER BY 1) sub;

When SQL performs row-to-column conversion, column names must be written out, and the actual SQL must be dynamically generated using the preprocessed SQL above before executing the actual SQL, which is cumbersome. Using stored procedures can merge two steps into one, but the structure is more complex.

SPL: The row-column conversion function of SPL does not need to write column names.