Using String_split and pivot function on comma separated value column-CodePudding

I am trying to string_split a comma separated value column then pivot the value of the result into a week, day , hour. Finally I need to group the numeric values by sub_issue_key and assignee. here is a sample of the data, the table name dbo.brut_data.

sub_task_key (unique values)	Assignee (only English letters)	Σ Remaining Estimate
dev-1	xzr ag	1 week, 3 days, 7 hours
dev-2	xzr ag	2 days, 6 hours
unit_test-1	ezs ak	6 hours

Here is what I did, this code splits the [Σ Remaining Estimate] column and returns it into multiple rows

SELECT sub_task_key, [Assignee],  value
FROM dbo.brut_data
CROSS APPLY STRING_SPLIT([Σ Remaining Estimate], ',') AS s

But I don't know how to pivot to match this result, could someone give me a helping hand thanks

Wanted results:

sub_task_key	Assignee	weeks	days	hours
dev-1	xzr ag	1	3	7
dev-2	xzr ag		2	6
unit_test-1	ezs ak			6

CodePudding user response：

Try the following using the STRING_SPLIT function and conditional aggregation to pivot the data:

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 '