This should work, but I really don't like your input data set. You are showing the text for colors AND their codes, the sizes AND their codes. Fortunately, the text and codes are in the same sequence so the WHERE clause works. If that sequence is ever broken, the Product column is not going to match the Code.

select replace(T.Product + ' (' + clr.item

+case when clr.item > '' and sz.item > '' then ',' else '' end

+sz.item+ ')'

,'()','') as Product

,SKU+cm1.item+sm1.Item as [Code]

from @t t

cross apply delimitedsplit8k(colour,',') clr

cross apply delimitedsplit8k(size,',') sz

cross apply delimitedsplit8k(cm,',') cm1

cross apply delimitedsplit8k(sm,',') sm1

where clr.itemnumber = cm1.itemnumber

and sz.itemnumber = sm1.itemnumber

You would be much better served by adding tables that contain both the codes and their text. For example

Create Table Sizes (SizeCode char(1) primary key, Size Varchar(10)

Insert Into Sizes

Values ('S','Small'),('M','Medium'),('L','Large')

Then your input could just be SKU, SizeCode, and ColorCode. The formula SKU+'-'+SizeCode+'-'+ColorCode would give the values for your [Code] result column and Joins would give you the elements of the full text for your [Product] result column. When you have some time, read up on normalization. Good luck.