I made use of the following technique described in stackoverflow.
https://stackoverflow.com/questions/13873701/convert-comma-separated-column-value-to-rows


CREATE TABLE MyTable ([Address] varchar(100), [MD] VARCHAR(100), [MK] VARCHAR(100));
INSERT INTO MyTable ([Address],[MD], [MK])
VALUES
('https://www.abc123.com/p/1/red-widget','This is a red widget','Red Cup, Bright Red Cup, Dark Red Cup'),
('https://www.abc123.com/p/1/small-red-widget','This is a small red widget','Bright Red Cup,Red Cup, Small Dark Red Cup'),
('https://www.abc123.com/p/1/big-red-widget','This is a big red widget','Big Red Round Cup, Bright Red Cup, Small Dark Red Cup, Dark Red Cups');

SELECT distinct 
  Split.a.value('.', 'VARCHAR(100)') AS String 
FROM (SELECT [address], 
   CAST ('<M>' + REPLACE([mk], ',', '</M><M>') + '</M>' AS XML) AS String 
  FROM mytable) AS A CROSS APPLY String.nodes ('/M') AS Split(a);