From SQL to SPL：Create columns from distinct values of a column

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

product	month	amount
AA	1	100
AA	1	150
AA	2	200
AA	2	120
BB	2	180
BB	2	220
CC	3	80

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

month	AA	BB	CC
1	250
2	320	400
3			80

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. The row column conversion function of SPL does not need to write column names: https://try.esproc.com/splx?4i8

	A
1	$select * from ventas.txt
2	=A1.pivot@s(month;product,sum(amount))

A1： Load data.

A2： Keep the month column unchanged, convert the products from row to column, and the amount is the column value. The pivot function is used for row to column conversion, @s represents aggregation operation during conversion.

Question source：https://stackoverflow.com/questions/78157389/create-columns-from-distinct-values-of-a-column