DEV Community

Cover image for Create Columns from Distinct Values of a Column — From SQL to SPL #26
2 1 1 1 1

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.

source table

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

expected results

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;
Enter fullscreen mode Exit fullscreen mode

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. 👉🏻Try.DEMO

esProc SPL code

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, and @s represents aggregation operation during conversion.


Get Started with esProc SPLFree Trial Download.

Runner H image

Ask Once. Get a Day Trip, Booked & Budgeted.

Want a kid-friendly Paris itinerary with a €100 limit? Runner H books, maps, plans, and syncs it all. Works with Google Maps, Airbnb, Docs & more.

Try Runner H

Top comments (1)

Collapse
 
judith677 profile image
Judith-Data-Processing-Hacks

Try esProc SPL yourself and share your experiences with us!

🔦Discord
🔦Reddit

Gen AI apps are built with MongoDB Atlas

Gen AI apps are built with MongoDB Atlas

MongoDB Atlas is the developer-friendly database for building, scaling, and running gen AI & LLM apps—no separate vector DB needed. Enjoy native vector search, 115+ regions, and flexible document modeling. Build AI faster, all in one place.

Start Free