SQL Server 2019

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

create table movies (
    ID int,
    title varchar(200),
    categories varchar(200)
)

insert into movies (ID, title, categories) values
(1,  'Movie A',   'Comedy, Drama, Romance'),
(2,  'Movie B',   'Animation'),
(3,  'Movie C',   'Documentary, Life changing')

3 rows affected

select * from movies
order by ID


ID
title
categories


1
Movie A
Comedy, Drama, Romance

2
Movie B
Animation

3
Movie C
Documentary, Life changing


WITH Source AS (
    SELECT
      m.ID,
      m.title,
      category = TRIM(cat.value),
      rn = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL))
    FROM movies m
    CROSS APPLY STRING_SPLIT(m.categories, ',') cat
)
MERGE movies t
USING Source s
ON s.ID = t.ID AND s.rn = 1
WHEN MATCHED THEN
  UPDATE
  SET categories = s.category
WHEN NOT MATCHED THEN
  INSERT (ID, title, categories)
  VALUES (s.ID, s.title, s.category)
;
6 rows affected

select * from movies
order by ID


ID
title
categories


1
Movie A
Comedy

1
Movie A
Drama

1
Movie A
Romance

2
Movie B
Animation

3
Movie C
Documentary

3
Movie C
Life changing

ID	title	categories
1	Movie A	Comedy, Drama, Romance
2	Movie B	Animation
3	Movie C	Documentary, Life changing

ID	title	categories
1	Movie A	Comedy
1	Movie A	Drama
1	Movie A	Romance
2	Movie B	Animation
3	Movie C	Documentary
3	Movie C	Life changing