Dynamic Pivoting of a Table

Because SQL fixes its column list at parse time, you cannot turn unknown row values (store names) into columns in a single static query — so you run two queries: the first reads the distinct store names and assembles a second query as a string, then PREPARE/EXECUTE compiles and runs that string. The pivot is metaprogramming: SQL writing SQL.

Problem

Table Products has columns product_id INT, store VARCHAR, price INT, with (product_id, store) as the primary key — so each product appears at most once per store. There are at most 30 distinct stores. Write a procedure PivotProducts that returns one row per product, with one column per store (the price there, or NULL if not sold), and the store columns sorted lexicographically.

The trap: you do not know the store names — or how many there are — until you query the data. A normal SELECT needs its columns spelled out in the source text, so the column list has to be generated before the pivot query is parsed.

The two-phase mechanism on real data

Take this concrete input (the same three products as the expected output):

Products
+------------+----------+-------+
| product_id | store    | price |
+------------+----------+-------+
| 1          | LC_Store | 100   |
| 1          | Shop     | 110   |
| 2          | Nozama   | 200   |
| 2          | Souq     | 190   |
| 3          | Shop     | 1000  |
| 3          | Souq     | 1900  |
+------------+----------+-------+

Phase 1 — read the schema-to-be. The distinct stores are LC_Store, Nozama, Shop, Souq. For each one, CONCAT stamps out a fragment of column SQL, and GROUP_CONCAT(... ORDER BY store ASC) glues the fragments into a single comma-separated string, alphabetised:

1. LC_Store → SUM(IF(store = "LC_Store", price, null)) AS LC_Store
2. Nozama → SUM(IF(store = "Nozama", price, null)) AS Nozama
3. Shop → SUM(IF(store = "Shop", price, null)) AS Shop
4. Souq → SUM(IF(store = "Souq", price, null)) AS Souq

That string is stored in @sql. Phase 2 wraps it with SELECT product_id, … FROM Products GROUP BY product_id and runs it. Now trace what the executed query does to product_id = 1: GROUP BY product_id collapses its two rows (LC_Store,100 and Shop,110) into one group, and each SUM(IF(...)) scans that group:

group product_id=1 = { (LC_Store,100), (Shop,110) }

SUM(IF(store="LC_Store", price, null))  = SUM(100, null) = 100
SUM(IF(store="Nozama",   price, null))  = SUM(null, null) = NULL
SUM(IF(store="Shop",     price, null))  = SUM(null, 110)  = 110
SUM(IF(store="Souq",     price, null))  = SUM(null, null) = NULL
  ->  | 1 | 100 | NULL | 110 | NULL |

The IF turns every row into either its price (matching store) or NULL (everything else); inside one product group at most one row matches a given store column, so SUM is just picking that single non-null value. SUM also ignores NULLs, so a store where the product is absent yields NULL, exactly as required.

The procedure

CREATE PROCEDURE PivotProducts()
BEGIN
    -- Default GROUP_CONCAT_MAX_LEN is only 1024 bytes; the
    -- generated query is far longer, so raise the cap first.
    SET group_concat_max_len = 1000000;

    SET @sql = NULL;

    -- PHASE 1: build the column list as a string
    SELECT GROUP_CONCAT(DISTINCT
             CONCAT('SUM(IF(store = "', store,
                    '", price, null)) AS ', store)
             ORDER BY store ASC)
    INTO @sql
    FROM Products;

    -- Wrap it into a full statement
    SET @sql = CONCAT('SELECT product_id, ', @sql,
                      ' FROM Products GROUP BY product_id');

    -- PHASE 2: compile and run the generated string
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

For this input, the string in @sql after the wrap is exactly:

SELECT product_id,
  SUM(IF(store = "LC_Store", price, null)) AS LC_Store,
  SUM(IF(store = "Nozama",   price, null)) AS Nozama,
  SUM(IF(store = "Shop",     price, null)) AS Shop,
  SUM(IF(store = "Souq",     price, null)) AS Souq
FROM Products
GROUP BY product_id

Output

+------------+----------+--------+------+------+
| product_id | LC_Store | Nozama | Shop | Souq |
+------------+----------+--------+------+------+
| 1          | 100      | NULL   | 110  | NULL |
| 2          | NULL     | 200    | NULL | 190  |
| 3          | NULL     | NULL   | 1000 | 1900 |
+------------+----------+--------+------+------+

Product 1 sells at LC_Store (100) and Shop (110); product 2 at Nozama (200) and Souq (190); product 3 at Shop (1000) and Souq (1900). Every other cell is NULL because that product is absent from that store — which is precisely what SUM over an all-null group returns.

Why the naive version is wrong

The instinct is to write the pivot directly: SELECT product_id, SUM(IF(store="Shop",price,null)) AS Shop, ... FROM Products GROUP BY product_id. That works only if you hard-code every store name into the query text — but the spec allows any of up to 30 unknown stores, and the column list is frozen when MySQL parses the query. There is no syntax for “one column per distinct value of store” in a static statement; the value list must be discovered by a query and re-emitted as new query text. That is the entire reason the dynamic-SQL machinery (GROUP_CONCAT → CONCAT → PREPARE) exists here.

A second naive slip: replacing SUM(IF(...)) with MAX(IF(...)) looks equivalent here, and it is — only because (product_id, store) is unique so each group has exactly one candidate. If that uniqueness did not hold, SUM would total all matching prices while MAX would keep the largest; they are different aggregations that happen to coincide under the key constraint.

Pitfalls

• group_concat_max_len silently truncates. The default is 1024 bytes. With ~30 stores the generated fragment string easily exceeds that, and GROUP_CONCAT chops it off without raising an error — leaving @sql holding a half-finished statement. PREPARE then fails with a confusing syntax error pointing at the middle of a column. Always raise the cap before building. (It is a session variable; set it inside the procedure.)
• Identifier vs. string quoting. The fragment uses double quotes for the string literal (store = "LC_Store"), which is fine under MySQL's default mode but breaks under ANSI_QUOTES, where "..." means an identifier. Portable code uses single quotes for the literal and backticks for the aliased column name: AS `', store, '` — otherwise a store named Shop 2 or one containing a quote produces invalid SQL.
• SQL injection through data. Store names flow straight into executed SQL text. A crafted value like x") AS a, (SELECT ... would inject. On a trusted internal table this is academic, but the same pattern in application code over user-supplied labels is a real vulnerability — sanitise or whitelist the pivot keys.
• A NULL @sql poisons the whole statement. If Products is empty, GROUP_CONCAT returns NULL, then CONCAT('SELECT ...', NULL, '...') returns NULL (any NULL argument makes CONCAT NULL), and PREPARE stmt FROM NULL errors. Guard with COALESCE or a row-count check if empty input is possible.
• Column-count blowup. Pivoting an unbounded key (e.g. user IDs instead of ~30 stores) can generate thousands of columns and hit MySQL's per-table column limit (4096) or a query too large to prepare. Dynamic pivot is safe only when the key cardinality is genuinely small and bounded.

Takeaways

• Static SQL fixes its columns at parse time; to pivot unknown values into columns you must run a query that writes a second query, then PREPARE/EXECUTE/DEALLOCATE it.
• SUM(IF(store = k, price, NULL)) under GROUP BY product_id is a conditional value-picker: it works as a pure selector only because (product_id, store) is unique, and yields NULL for absent stores for free.
• GROUP_CONCAT with DISTINCT + ORDER BY both de-duplicates the keys and enforces the lexicographic column order in one pass — but it silently truncates past group_concat_max_len, the single most common reason this pattern breaks.
• Native PIVOT exists in SQL Server / Oracle but still needs dynamic SQL for unknown columns; MySQL and Postgres have no PIVOT keyword, so this hand-rolled idiom is the standard answer there.

Sources: MySQL 8.0 Reference Manual — GROUP_CONCAT() and group_concat_max_len, IF()/aggregate-function semantics, and the PREPARE/EXECUTE/DEALLOCATE PREPARE prepared-statement chapter; LeetCode 1543/3061 “Dynamic Pivoting of a Table” problem statement and editorial discussion. Re-authored and deepened for this guide: added a value-traced GROUP BY walkthrough and mechanism diagram, a “why the naive version is wrong” note (static column binding; SUM vs MAX under the unique-key constraint), and concrete failure modes (truncation, ANSI_QUOTES, injection, NULL @sql, column-count limits). Corrected a factual error in the prior version, whose “Interpretation” described a non-existent product_id = 4 (Shop 200 / Souq 300) that never appears in the three-row output.