Developer ToolsFree generatorPDF, Excel, Word exportTextile and Apparel

Apparel Style, Color and Size SKU Matrix SQL Generator

An apparel style is sold as a grid of colors and sizes, and every cell needs its own SKU. This generator builds the cross join, inserts only the missing rows and checks the result, so a new style takes seconds instead of hours of typing.

Fill in

SKU table

Generic name. Replace with your own variant or item table.

Style code

Colors as VALUES list

SQL Server VALUES syntax, one tuple per color.

Sizes with sort order as VALUES list

Each tuple is size code and sort order.

Generated code

Download:

Preview the matrix before inserting

SELECT 'TS1042' + '-' + c.color_code + '-' + s.size_code AS sku,
       'TS1042' AS style_code,
       c.color_code,
       s.size_code,
       s.size_sort
FROM (VALUES ('BLK'),('NVY'),('WHT'),('GRY')) AS c(color_code)
CROSS JOIN (VALUES ('XS',1),('S',2),('M',3),('L',4),('XL',5),('XXL',6)) AS s(size_code, size_sort)
ORDER BY c.color_code, s.size_sort;

Insert only the missing SKUs

INSERT INTO dbo.sku_master (sku, style_code, color_code, size_code, size_sort)
SELECT 'TS1042' + '-' + c.color_code + '-' + s.size_code,
       'TS1042',
       c.color_code,
       s.size_code,
       s.size_sort
FROM (VALUES ('BLK'),('NVY'),('WHT'),('GRY')) AS c(color_code)
CROSS JOIN (VALUES ('XS',1),('S',2),('M',3),('L',4),('XL',5),('XXL',6)) AS s(size_code, size_sort)
WHERE NOT EXISTS (
  SELECT 1
  FROM dbo.sku_master x
  WHERE x.sku = 'TS1042' + '-' + c.color_code + '-' + s.size_code
);

Check for duplicates and count

SELECT sku, COUNT(*) AS copies
FROM dbo.sku_master
WHERE style_code = 'TS1042'
GROUP BY sku
HAVING COUNT(*) > 1;

SELECT COUNT(*) AS sku_count
FROM dbo.sku_master
WHERE style_code = 'TS1042';

Table and column names are generic and the VALUES syntax is T-SQL (SQL Server). On Oracle, PostgreSQL or MySQL, replace the VALUES derived table with a UNION ALL list or the equivalent syntax. Run the preview first, test in a non-production database, and map the columns to your own item or variant table. Prefer your ERP's import or API route for production item creation.

Variant creation automation

Optional: a walkthrough of how to automate style and variant creation in your ERP using supported interfaces.

Optional. The tool and every export work without this. No spam.

How to use the generator

Enter the SKU table, the style code, the colors and the size run as VALUES lists. The three code blocks update as you type. Run the preview first to see every SKU the matrix will create. If the grid looks right, run the insert, which uses NOT EXISTS so it can be repeated safely when a color or size is added later. Finish with the duplicate check and the count, which should equal colors multiplied by sizes for a fresh style.

Preview before insert
Insert skips SKUs that already exist
Count should equal colors times sizes
Keep size sort order so grids print in the right sequence

How the SQL works

A cross join pairs every row of one set with every row of another. Two derived tables built with VALUES supply colors and sizes, and the cross join yields every combination, so four colors and six sizes produce 24 SKUs. The SKU string is the style, color and size joined with hyphens. The NOT EXISTS clause compares the generated SKU to the table, which makes the script idempotent and protects against duplicate keys.

Common mistakes and ERP notes

Do not insert directly into an ERP's item tables unless the vendor supports it, because items often have related records, such as unit conversions, costs and planning data, that direct inserts skip. Use this output to stage and validate a load file or to build an import sheet. Also keep size codes consistent, as mixing XXL and 2XL in the same system splits stock and reporting.

Use the ERP's import or API for production item creation
Standardize size codes before you generate
Store size sort order so grids and reports line up

Frequently Asked Questions

What is a SKU matrix in apparel?

A SKU matrix is the grid of every color and size combination for a style. Each cell is a separate stock-keeping unit with its own inventory, barcode and price, so a style with 4 colors and 6 sizes has 24 SKUs.

Can I use this script on Oracle or PostgreSQL?

The structure works, but the VALUES derived table syntax shown is T-SQL. In Oracle, use SELECT FROM DUAL with UNION ALL, and in PostgreSQL the VALUES list works with slightly different alias syntax. Test in a non-production schema first.

How do I add a new size later?

Put only the new size in the sizes list, keep the full color list, and run the insert. The NOT EXISTS check skips existing SKUs, so only the new size column of the grid is created.

Should I insert straight into my ERP item table?

Usually no. ERP items have linked records for units, costing, planning and tax. Use the supported import tool or API, and treat this SQL as a way to build and check the data first.

If creating styles and variants in your ERP is slow or error-prone, we can look at how to automate the matrix creation through supported import routes.

Talk to an Expert Explore More Free Tools

Related Tools

MES & Shop Floor

More Developer Tools tools

See all

Back to All Tools