尝试使用STRING_SPLIT函数和条件聚合透视数据：

SELECT T.sub_task_key, T.Assignee,
  ISNULL
  (
    MAX(Case When S.value Like '%week%' Then TRIM('weeks ' FROM S.value) End) 
    , 0
  ) AS weeks,
  ISNULL
  (
    MAX(Case When S.value Like '%day%' Then TRIM('days ' FROM S.value) End) 
    , 0
  ) AS days,
  ISNULL
  (
    MAX(Case When S.value Like '%hour%' Then TRIM('hours ' FROM S.value) End) 
    , 0
  ) AS hours
FROM brut_data T CROSS APPLY 
     STRING_SPLIT(T.Remaining_Estimate, ',') S
GROUP BY T.sub_task_key, T.Assignee
ORDER BY T.sub_task_key, T.Assignee

或者，可以按如下方式使用透视运算符：

SELECT sub_task_key, Assignee,
 ISNULL(TRIM('weeks ' FROM w), 0) AS weeks,
 ISNULL(TRIM('days ' FROM d), 0) AS days,
 ISNULL(TRIM('hours ' FROM h), 0) AS hours  
FROM
( 
  SELECT T.sub_task_key, T.Assignee,
         S.value,
         CASE 
          WHEN S.value LIKE '%week%' THEN 'w'
          WHEN S.value LIKE '%day%' THEN 'd'
          WHEN S.value LIKE '%hour%' THEN 'h'
         END AS intervals 
  FROM brut_data T CROSS APPLY 
       STRING_SPLIT(T.Remaining_Estimate, ',') S
) D
PIVOT 
(
  MAX(value) FOR intervals IN ([w], [d], [h])
) P
ORDER BY sub_task_key, Assignee

See demo