SQL Server Query for required result

Question

I am using SQL Server with my application.

The Table data is as following :

And I want result in following format:

I have tried with split function but its not working properly.

Is it possible to get such a result.

Please suggest.

Thank you.

Answer 1

Try this. I did not manage to get a single Not Req, it is like this ("Not Req/Not Req").

drop table if exists dbo.TableB;

create table dbo.TableB (
OldSPC varchar(100)
, old_freq varchar(100)
, NewSPC varchar(100)
, new_freq varchar(100)

);

insert into dbo.TableB(OldSPC, old_freq, NewSPC, new_freq)
values ('ADH,BAP', '7,7', 'ADH,BAP', '7,7')
, ('Not Req', 'Not Req', 'ADH,BAP', '7,7')
, ('BAP,EXT,ADL', '35,7,42', 'BAP,EXT,BAP,ADL', '21,7,35,42');

select
tt1.OldSPCOldFreq
, tt2.NewSPCNewFreq
from (
    select
        t.OldSPC, t.old_freq, t.NewSPC, t.new_freq
        , STRING_AGG(t1.value + '/' + t2.value, ',') OldSPCOldFreq
    from dbo.TableB t
        cross apply (
            select
                ROW_NUMBER () over (order by t.OldSPC) as Rbr
                , ss.value
            from string_split (t.OldSPC, ',') ss
        ) t1
        cross apply (
            select
                ROW_NUMBER () over (order by t.old_freq) as Rbr
                , ss.value
            from string_split (t.old_freq, ',') ss
        ) t2
    where t1.Rbr = t2.Rbr
    group by t.OldSPC, t.old_freq, t.NewSPC, t.new_freq
) tt1
inner join (
    select
        t.OldSPC, t.old_freq, t.NewSPC, t.new_freq
        , STRING_AGG(t3.value + '/' + t4.value, ',') NewSPCNewFreq
    from dbo.TableB t
        cross apply (
            select
                ROW_NUMBER () over (order by t.NewSPC) as Rbr
                , ss.value
            from string_split (t.NewSPC, ',') ss
        ) t3
        cross apply (
            select
                ROW_NUMBER () over (order by t.new_freq) as Rbr
                , ss.value
            from string_split (t.new_freq, ',') ss
        ) t4
    where t3.Rbr = t4.Rbr
    group by t.OldSPC, t.old_freq, t.NewSPC, t.new_freq
) tt2 on tt1.OldSPC = tt2.OldSPC
and tt1.old_freq = tt2.old_freq
and tt1.NewSPC = tt2.NewSPC
and tt1.new_freq = tt2.new_freq