Requesting T SQL Logic for the below scenario

Question

Hi All,

I have data like below.

issue_id	Created	        from_string	           to_string  
420145	2018-06-13	null	                          EPC-Management  
420145	2018-11-18	null	                          L3-7  
420145	2018-11-18	EPC-Management	  null  
420145	2018-11-18	null	                          L2-4  
420145	2018-11-21	L3-7	                          null  
420145	2018-11-21	null	                          Management  
420145	2018-11-25	Management	          null  
420145	2018-11-25	null	                          XM  
  

Need to get the output like below

issue_id	Created	        final_string  
420145	2018-06-13	EPC-Management  
420145	2018-11-18	L3-7,L2-4  
420145	2018-11-21	L2-4,Management  
420145	2018-11-25	L2-4,XM  
  

Ex: On 2018-06-13, issue is having component EPC-Management, So output for this record will be EPC-Management
on 2018-11-18 , issue is having 3 records in the source . Two components added (L3-7, L2-4) and previous component removed .
So output is L3-7,L2-4
on 2018-11-21 , issue is having 2 records in the source . One component added (Management) and one component removed (L3-7) .
So output is L2-4,Managment
on 2018-11-25 , issue is having 2 records in the source . One component added (XM) and one component removed (Management) .
So output is L2-4,XM

Could any one please help me on this

Answer 1

To obtain reliable results for new scenario, I think that it is necessary to have a column that orders the rows (for example a Created with distinct time part).

The next variation relies on default ordering:

;
with T1 as
(
    select *, 
        row_number() over (partition by issue_id order by Created) i
    from #temp
),
T2 as
(
    select *, 
        row_number() over (partition by issue_id, Created order by i desc) j
    from T1
),
T3 as
(
    select *, cast(isnull(to_string, '') as varchar(max)) as final_string
    from T2
    where i = 1
    union all
    select T2.*, 
        cast(stuff((select ',' + s from (
            select value from string_split(T3.final_string, ',') 
            except 
            select * from (values (T2.from_string)) d(s) 
            union all
            select * from (values (T2.to_string)) d(s)
        ) d(s) for xml path('')), 1, 1, '') as varchar(max))
    from T2
    inner join T3 on t3.issue_id = t2.issue_id and T3.i+1 = T2.i
)
select issue_id, Created, final_string
from T3
where j = 1
order by issue_id, i

Results:

/*

issue_id    Created final_string
420145  2018-06-13  EPC-Management
420145  2018-11-18  L3-7,L2-4
420145  2018-11-21  L2-4,Management
420145  2018-11-25  L2-4,XM
420145  2018-12-10  XM
420145  2018-12-23  XM,L2-4

*/

Answer 2

Try a query:

select issue_id, Created,
    stuff((select ', ' + s from
    (
     select to_string as s from MyTable where issue_id = t.issue_id and Created <= t.Created
     except
     select from_string from MyTable where issue_id = t.issue_id and Created <= t.Created
    ) d for xml path('')), 1, 2, '') as final_string 
from MyTable t
group by issue_id, Created
order by issue_id, Created

Optimise it, and consider STRING_AGG too.

Answer 3

Hi ,

Thanks for providing your inputs . For most of the scenarios it is working, but not getting expected result If the same component is removed and added again later

drop table if exists #temp
create table #temp
(
issue_id varchar(100)
,Created date
,from_string varchar(100)
,to_string varchar(100)
)
insert into #temp select '420145','2018-06-13',NULL,'EPC-Management'
insert into #temp select '420145','2018-11-18',NULL,'L3-7'
insert into #temp select '420145','2018-11-18','EPC-Management',NULL
insert into #temp select '420145','2018-11-18',NULL,'L2-4'
insert into #temp select '420145','2018-11-21','L3-7',NULL
insert into #temp select '420145','2018-11-21',NULL,'Management'
insert into #temp select '420145','2018-11-25','Management',NULL
insert into #temp select '420145','2018-11-25',NULL,'XM'
insert into #temp select '420145','2018-12-10','L2-4',NULL
insert into #temp select '420145','2018-12-23',NULL,'L2-4'

select * from #temp

select issue_id, Created,
stuff((select ', ' + s from
(
select to_string as s from #temp where issue_id = t.issue_id and Created <= t.Created
except
select from_string from #temp where issue_id = t.issue_id and Created <= t.Created
) d for xml path('')), 1, 2, '') as final_string
from #temp t
group by issue_id, Created
order by issue_id, Created

In the below output , we need to get L2-4 ,XM for 2018-12-23.

Also os it possible without correlated subquery. Because we are using Big Query which is not supported corelated subqueries

Thanks

Answer 4

Hi @Rajesh Chittineni
Please also check this query:

;WITH CTE1 AS  
(  
SELECT DISTINCT issue_id,Created  
      ,DENSE_RANK()OVER(ORDER BY issue_id,Created)RNum  
      ,(SELECT DISTINCT ','+from_string FROM #temp T2 WHERE T2.issue_id=T1.issue_id AND T2.Created=T1.Created FOR XML PATH('')) from_string   
      ,(SELECT DISTINCT ','+to_string FROM #temp T2 WHERE T2.issue_id=T1.issue_id AND T2.Created=T1.Created FOR XML PATH('')) to_string       
FROM #temp T1  
),CTE2 AS  
(  
SELECT issue_id,Created,ISNULL(to_string,'') AS Final_string,1 AS LVL  
FROM CTE1   
WHERE RNUM =1  
UNION ALL  
SELECT C1.issue_id,C1.Created,REPLACE(C2.Final_string+ISNULL(C1.to_string,''),ISNULL(C1.from_string,'')+',',','),LVL+1 AS LVL  
FROM CTE2 C2 JOIN CTE1 C1 ON C1.RNum=C2.LVL +1  
)  
SELECT issue_id,Created,SUBSTRING(Final_string,2,LEN(Final_string)-1) AS Final_string  
FROM CTE2  

Output:

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

Hi Viorel and LiHong,

Thanks for providing the logic. It is working for most of the scenarios and checking few more others scenarios. I will check and will post you if any issues.

Thanks for you