CREATE TABLE dept_income (
    dept_id   INT,
    mon       VARCHAR(3),
    income    INT
);

INSERT INTO dept_income VALUES
(1,'Jan',8000),(1,'Feb',7000),(1,'Mar',6000),
(2,'Jan',9000),(2,'Feb',8000),(2,'Mar',7000);

The goal is to transform the above row-oriented data into a single row per department with twelve monthly columns.

Why GROUP BY Restricts SELECT *

When GROUP BY is applied, the database collapses all rows that share the same grouping key(s) into one logical group. After this step the engine has multiple cendidate values for every non-grouped column. Because a relational cell must contain exactly one scalar value, the query must either:

• include the column in the GROUP BY list, or
• reduce the set of candidate values to a single scalar through an aggregate function (SUM, MAX, MIN, …).

Selecting * after grouping therefore fails unless every column is either grouped or aggregated.

Visualising the Virtual Grouped Table

Imagine the following intermediate result after grouping by dept_id:

This table cannot be materialised; it exists only conceptually to understand how aggregates operate.

Conditional Aggregation for Pivoting

The pivot is achieved by runing a conditional aggregate over each month:

SELECT
    dept_id,
    SUM(CASE WHEN mon = 'Jan' THEN income END) AS Jan_Income,
    SUM(CASE WHEN mon = 'Feb' THEN income END) AS Feb_Income,
    SUM(CASE WHEN mon = 'Mar' THEN income END) AS Mar_Income,
    SUM(CASE WHEN mon = 'Apr' THEN income END) AS Apr_Income,
    SUM(CASE WHEN mon = 'May' THEN income END) AS May_Income,
    SUM(CASE WHEN mon = 'Jun' THEN income END) AS Jun_Income,
    SUM(CASE WHEN mon = 'Jul' THEN income END) AS Jul_Income,
    SUM(CASE WHEN mon = 'Aug' THEN income END) AS Aug_Income,
    SUM(CASE WHEN mon = 'Sep' THEN income END) AS Sep_Income,
    SUM(CASE WHEN mon = 'Oct' THEN income END) AS Oct_Income,
    SUM(CASE WHEN mon = 'Nov' THEN income END) AS Nov_Income,
    SUM(CASE WHEN mon = 'Dec' THEN income END) AS Dec_Income
FROM dept_income
GROUP BY dept_id
ORDER BY dept_id;

How it works

1. GROUP BY dept_id creates one logical row per department.
2. For each logical row, the CASE expression in side SUM filters the array-like income values, keeping only those whose corresponding month matches the target month.
3. SUM then collapses the filtered set (usually a single value) into the scalar cell required by SQL semantics.

The result:

Omitting the aggregate and writing only CASE WHEN mon='Feb' THEN income END would return the first matching value encountered in the group, which is unreliable and often yields NULL when the desired month is not the first element.