t, Sql Server - rextester

Language: Layout:

CREATE TABLE product
    ([pd_code] varchar(7), [Height] int, [width] int, [length] int, [pd_catDesc] varchar(39), [category] varchar(92))
;
    
INSERT INTO product
    ([pd_code], [Height], [width], [length], [pd_catDesc], [category])
VALUES
    ('TC TSPM', 67, 3000, 76, 'Traditional Cornice Pumice Plain Shaker', 'Kitchens >> Front Matching Features >> Front Matching Features >> Traditional Cornice___1959'),
    ('TC TSPM', 67, 3000, 76, 'Traditional Cornice Pumice Plain Shaker', 'Kitchens >> Front Matching Features >> Front Matching Features >> Traditional Cornice___1967'),
    ('TC TSPM', 67, 3000, 76, 'Traditional Cornice Pumice Plain Shaker', 'Kitchens >> Front Matching Features >> Front Matching Features >> Traditional Cornice___1968')
;

WITH trim_cat as (
    SELECT [pd_code], [Height], [width], [length], [pd_catDesc],
           REVERSE(LEFT(reverse(category), charIndex(' >>', reverse(category)) -1 )) as [category]
    FROM PRODUCT
)
SELECT DISTINCT
      [pd_code], [Height], [width], [length], [pd_catDesc],
      [category] = (
          SELECT tc.[category] + ' ; '
          FROM trim_cat tc
          WHERE tc.[pd_code] = t.[pd_code]          
          FOR XML PATH('')          
          )
FROM trim_cat t

View schema
Execution time: 0,02 sec, rows selected: 1, rows affected: 3, absolute service time: 0,19 sec