I tried searching and found a lot of solutions for problems with "identical" id, but my problem is dealing with "similar" ids.

I have a table with a bunch of tasks, task description, and procedure notes. Each task may have multiple sub-tasks. I need to combine all subtask information into one cell. The table looks like below:

Desired Result (formatting keeps breaking sorry):

| TaskID | Description |

| Task-1 | Step 1 Step 1 Notes, Step 2 Step 2 Notes, Step 3 Step 3 Notes |

Since the naming of TaskID is not consistent I can't just trim and compare. I have tried using XML PATH / STUFF, but that only lets me do one task at a time and I have hundreds. I also don't have access to STRING_AGG.

Any help is appreciated. Thanks!

CodePudding user response：

Will the procedure notes always null for the parent task row and not null for sub-task rows? If so, you can query the parent task separately. Then for the 'child' rows where 'Procedure notes' is not null, use the below advise to remove the last part of the value from task ID. you should be able to link your child row to the parent row.

Split string and take last element

CodePudding user response：

So I a assumed three things:

• there are two levels; top level tasks and sub tasks
• each task has null as procedure notes
• task name are included in sub-task name and it is not possible that task name can be part of another task name

based on these assumptions below is my attempt and a db<>fiddle-

; with findtaskid as 
(
select taskid,cast('' as nvarchar(max)) as notes ,
row_number() over (order by t.taskid) as id,
cast(0 as int) as r2
from t 
where ProcedureNotes is NULL

union all 

select 
t.taskid, 
ISNULL(t.description,'')  ' '  ISNULL(t.procedurenotes,'') Notes,
id,
cast(row_number() over (order by t.taskid) as int) as r2
from 
findtaskid c join t 
on c.taskid<>t.taskid
and t.taskid like c.taskid  '%'
),
collates as
(
select taskid, notes, r2,id 
from findtaskid 
where r2=1

union all

select c.taskid, c.notes  '  '  f.notes, f.r2, f.id
from findtaskid f join collates c
on c.id=f.id and f.r2=c.r2 1 

), lastrowoutput as
(
select taskid,notes, row_number() over (partition by id order by r2 desc) as r3
from collates

)
select taskid,notes from lastrowoutput where r3=1