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

As mentioned in comments, it might be easier for you to do it on front end, but it could be done in SQL Server as well.

Partial Rextester Demo

I didn't replicate your whole scenario but got it for 2 columns. To do it first of all, you need a unique identifier for each row. I am using a sequence number (1,2,3...).

Now refer to this answer, which uses recursive subquery to split csv to rows. Then I used XML PATH to change columns back to csv.

This is the query which is doing it for OLD SPC and OLD FREQ.

;with tmp(SEQ,OldSPCItem,OldSPC,OLD_FREQ_item,OLD_FREQ) as (
select SEQ, LEFT(OldSPC, CHARINDEX(',',OldSPC+',')-1),
    STUFF(OldSPC, 1, CHARINDEX(',',OldSPC+','), ''),

    LEFT(OLD_FREQ, CHARINDEX(',',OLD_FREQ+',')-1),
    STUFF(OLD_FREQ, 1, CHARINDEX(',',OLD_FREQ+','), '')
from table1
union all
select SEQ,  LEFT(OldSPC, CHARINDEX(',',OldSPC+',')-1),
    STUFF(OldSPC, 1, CHARINDEX(',',OldSPC+','), ''),
        LEFT(OLD_FREQ, CHARINDEX(',',OLD_FREQ+',')-1),
    STUFF(OLD_FREQ, 1, CHARINDEX(',',OLD_FREQ+','), '')
from tmp
where OldSPC > ''
)
select seq,STUFF( (SELECT ',' + CONCAT(OldSPCItem,'/',OLD_FREQ_item) FROM TMP I 
 WHERE I.seq = O.seq FOR XML PATH('')),1,1,'') OLD_SPC_OLD_FREQ
from tmp O
GROUP BY seq
;

It will give you this output

+-----+------------------+
| seq | OLD_SPC_OLD_FREQ |
+-----+------------------+
|   1 | ADH/7,BAP/9      |
|   2 | NOT REQ/NOT REQ  |
+-----+------------------+

What do you have to do now - Find a way to generate a sequence number to uniquely identify each row. If you can use any column, use that instead of SEQ.

• Similarly add logic for NEW_SPC and NEW_FREQ. (just copy paste LEFT and STUFF like in OLD_FREQ and change it for NEW_SPC and NEW_FREQ.

• Replace multiple NOT REQ/ with '', so you will get only one NOT REQ. You can do it with replace function.

If you face any issue/error while doing so, add it to the Rexterster Demo and share the URL, we will check that.

Answer 2

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