CROSS APPLY + SPLIT STRING

create table #temp (ID int, Brands varchar(500))

insert into #temp (ID, Brands) values  (1,'FORD,CHEVROLET')

insert into #temp (ID, Brands) values  (2,'FIAT, VW,PEUGEOT')

insert into #temp (ID, Brands) values  (3,'FERRARI')

select ID, LTRIM(rtrim(value)) as Brand 

from #temp

CROSS APPLY STRING_SPLIT(Brands, ',');