Pivot rows to columns without PIVOT

Aggregate with a CASE expression per output column. It works on every SQL engine. Many engines also have a built-in PIVOT operator that writes the same thing more shortly.

Portable version

Input monthly_sales(product, month, amount):

product  month  amount
Pen      Jan    100
Pen      Feb    120
Book     Jan     80

SELECT product,
       SUM(CASE WHEN month = 'Jan' THEN amount END) AS jan,
       SUM(CASE WHEN month = 'Feb' THEN amount END) AS feb,
       SUM(CASE WHEN month = 'Mar' THEN amount END) AS mar
FROM monthly_sales
GROUP BY product;

Output: Pen with 100, 120, NULL and Book with 80, NULL, NULL. No ELSE means a month with no data returns NULL. Add COALESCE(..., 0) if you want zeros.

PIVOT operator

Snowflake, BigQuery, SQL Server and Databricks support it:

SELECT *
FROM monthly_sales
PIVOT (SUM(amount) FOR month IN ('Jan', 'Feb', 'Mar'));

The syntax differs a bit between engines, so check the details of the one you use.

The limit: the columns are fixed

Both forms need you to list the months in the query. SQL has one rule here: the number and names of output columns are fixed when the query is parsed, not decided by the data. If new values appear (a new month or a new product), you either edit the query or generate the SQL text yourself, using a first query to list the values and then building the second as a string. Snowflake's PIVOT ... FOR month IN (ANY) handles this in some cases. In pipelines, tools like dbt can generate the column list with a Jinja loop. Wide results with hundreds of columns are usually a sign that the data should stay long and be pivoted in the BI tool.

Reverse: unpivot

SELECT product, 'Jan' AS month, jan AS amount FROM wide
UNION ALL
SELECT product, 'Feb', feb FROM wide;

Or use the UNPIVOT operator where available. Wide to long is what you want before most analytics.