Dynamic Store Price Pivot Map

A retail intelligence team receives product pricing in long format, where each row represents one product-store quote. They need a dynamic pivot view that is stable for downstream systems even when the set of stores changes over time.

Table: Products

• product_id: product identifier
• store: store name
• price: product price in that store

Data guarantees:

• (product_id, store) is unique.
• There are at most 30 distinct stores in one input.

Task: Build a canonical dynamic pivot string per product.

Definitions:

• Let U be the set of all distinct stores in Products.
• Sort U lexicographically.
• For each product_id, generate store_price_map by traversing sorted U and emitting:
  • store=price if that product has a row for that store
  • store=NULL if it does not
• Join all tokens with semicolons.

Output requirements:

• Return exactly these columns: product_id, store_price_map
• Sort by product_id ascending.

Supported submission environments:

• SQL (SQLite 3.27.2)
• Pandas (Python)

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

[
  {"product_id":1,"store_price_map":"LC_Store=100;Nozama=NULL;Shop=110;Souq=NULL"},
  {"product_id":2,"store_price_map":"LC_Store=NULL;Nozama=200;Shop=NULL;Souq=190"},
  {"product_id":3,"store_price_map":"LC_Store=NULL;Nozama=NULL;Shop=1000;Souq=1900"}
]

Products:
| product_id | store | price |
|------------|-------|-------|
| 10         | A1    | 7     |
| 10         | A10   | 5     |
| 11         | A2    | 9     |

[
  {"product_id":10,"store_price_map":"A1=7;A10=5;A2=NULL"},
  {"product_id":11,"store_price_map":"A1=NULL;A10=NULL;A2=9"}
]

Products:
[]

[]