Requesting T SQL Logic for the below scenario

Rajesh Chittineni 141 Reputation points
2022-04-26T19:59:47.667+00:00

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  
  

196660-image.png

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  
  

196741-image.png

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 114.5K Reputation points
    2022-04-27T06:02:44.057+00:00

    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
    
    */
    
    0 comments No comments

5 additional answers

Sort by: Most helpful
  1. Viorel 114.5K Reputation points
    2022-04-26T20:29:01.763+00:00

    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.

    0 comments No comments

  2. Rajesh Chittineni 141 Reputation points
    2022-04-26T21:51:00.18+00:00

    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

    196694-image.png

    Thanks

    0 comments No comments

  3. LiHong-MSFT 10,046 Reputation points
    2022-04-27T05:48:46.317+00:00

    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:
    196841-image.png

    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.

    0 comments No comments

  4. Rajesh Chittineni 141 Reputation points
    2022-04-28T15:27:29.567+00:00

    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

    0 comments No comments