SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE unnormalized(vendor_tag TEXT, vendor_tag_name TEXT, vendor_id INT);
✓
INSERT INTO unnormalized VALUES
('5,8,30,24','Burgers,Desserts,Fries,Salads',1),
('5','Burgers',2),
('8,42','Desserts,Mexican',3),
('1,5,30,16','American,Burgers,Fries,Sandwiches',4),
('1,5,30,16','American,Burgers,Fries,Sandwiches',5);
✓
WITH cte AS (
  SELECT 
    vendor_tag, 
    vendor_tag_name,
    SUBSTR(vendor_tag, 1, INSTR(vendor_tag || ',', ',') - 1) col1,
    SUBSTR(vendor_tag_name, 1, INSTR(vendor_tag_name || ',', ',') - 1) col2
  FROM unnormalized 
  UNION ALL 
  SELECT 
    SUBSTR(vendor_tag, LENGTH(col1) + 2), 
    SUBSTR(vendor_tag_name, LENGTH(col2) + 2), 
    SUBSTR(SUBSTR(vendor_tag, LENGTH(col1) + 2), 1, INSTR(SUBSTR(vendor_tag, LENGTH(col1) + 2) || ',', ',') - 1),
    SUBSTR(SUBSTR(vendor_tag_name, LENGTH(col2) + 2), 1, INSTR(SUBSTR(vendor_tag_name, LENGTH(col2) + 2) || ',', ',') - 1)
  FROM cte	
  WHERE LENGTH(vendor_tag) AND LENGTH(vendor_tag_name)
)
SELECT DISTINCT col1 vendor_tag, col2 vendor_tag_name
FROM cte
WHERE NOT (INSTR(col1, ',') OR INSTR(col2, ',')) AND (LENGTH(col1) AND LENGTH(col2))
ORDER BY vendor_tag + 0


vendor_tag
vendor_tag_name


1
American

5
Burgers

8
Desserts

16
Sandwiches

24
Salads

30
Fries

42
Mexican

vendor_tag	vendor_tag_name
1	American
5	Burgers
8	Desserts
16	Sandwiches
24	Salads
30	Fries
42	Mexican