SQL Grouping rows in a feed database table, output only grouped rows adding a group row

Question

I'm trying to write an SQL query to work on a raw feed that's been imported to a MySQL database table. The table's rows loosely resemble this example.

| sku | group_name | description | size | type |
|------------|------------|-------------------|------|--------|
| EX-1000-XS | NULL | Long text | XS | single |
| EX-1000-S | NULL | Long text | S | single |
| EX-1000-M | NULL | Long text | M | single |
| EX-1000-L | NULL | Long text | L | single |
| EX-1001-M | EX-1001 | Another long text | M | single |
| EX-1001-L | EX-1001 | Another long text | L | single |
| EX-1001 | NULL | | | group |

I'm trying to get the ones without a group_name, but with the determined group name set, along with a group row. Ignoring stuff that already was in a grouping. So the output I want from the query is like this.

| sku | group_name | description | size | type |
|------------|------------|-------------------|------|--------|
| EX-1000-XS | EX-1000 | Long text | XS | single |
| EX-1000-S | EX-1000 | Long text | S | single |
| EX-1000-M | EX-1000 | Long text | M | single |
| EX-1000-L | EX-1000 | Long text | L | single |
| EX-1000 | NULL | | | group |

I've figured out some substring function on the sku column, to get a "key" like value,

SELECT
 SUBSTRING_INDEX(
 sku,
 '-',
 LENGTH(sku) - LENGTH(REPLACE(sku, '-', ''))
 )
FROM
 products
WHERE
 group_name IS NULL
 AND type = "single";

No idea how to use that as a grouping key yet,

I set up a fiddle with the example, https://www.db-fiddle.com/f/ua6iwXGHcPDVKbCkqHvs2E/0

• user153556: Can you elaborate what grouping key?
• J. M. Becker: @sam, the key a substring of the sku, to be used to identify a group.

Answer 1

db-fiddle has strange effect on columns are not in order as per sql statement but works fine on my local Mysql 8.0

select sku,
 case WHEN group_name IS NULL
 AND type='single'
 THEN TRIM(TRAILING '-' FROM REPLACE(sku,size,''))
 ELSE group_name 
 END 
 AS group_name,
 description,
 size,
 type
from products

This gives your desired output with cte expressions i.e excluding existing rows plus grouping key(same output as bottom table in your question).Tested on your dbfiddle

WITH cte1
AS
 (
 SELECT sku,
 TRIM(TRAILING '-' FROM REPLACE(sku,size,'')) 
 group_name,
 description,
 size,
 type
 FROM products
 WHERE group_name IS NULL
 AND type = 'single'
 )
SELECT * FROM cte1
UNION
SELECT DISTINCT(group_name) sku,
 NULL group_name,
 '' description,
 '' size,
 'group' type
FROM cte1 

• J. M. Becker: OK, I see the idea you did there, using the specific attribute column data to get the substring. That is an idea that might be better than stripping the right most delimiter, which is what I was doing. It's close-ish, I really can't select the excluded stuff though, it's just a rough example, the real data has many more columns. I think I'm going to need to create a header row, append it to the output, and then do something like your case as well.
• user153556: You can use both options step 1 will include all rows and union with grouping key(it needs to be modified a little further with cte expression) or step 2 excluding existing non null values in group_name column along with grouping key.
• J. M. Becker: Thank you for your help, I'm gonna use this method.

Answer 2

SELECT * FROM products WHERE group_name IS NOT NULL
UNION ALL
SELECT * FROM products WHERE group_name IS NULL AND type = 'group';

• J. M. Becker: You got the part I didn't want, appreciate the effort though. In other words, the output should look like the lower table example.