question

kasimmohamed-6993 avatar image
0 Votes"
kasimmohamed-6993 asked EchoLiu-msft commented

Look the comma seperated value and bring unique value into second column

Hi,

I have two table like #table1 and #table2 like below


create table #table1 (tbl1Data nvarchar(100));
create table #table2 (tbl2Data nvarchar(100));
insert into #table1 values ('R07.9,R42');
insert into #table1 values ('E07.9,Z83.3,Z82.49');
insert into #table1 values ('R10.32');
insert into #table1 values ('S00.81XA,S59.802A,S13.9XXA');

insert into #table2 values ('R42');
insert into #table2 values ('Z82.49');
insert into #table2 values ('R42');
insert into #table2 values ('S00.81XA');
insert into #table2 values ('S59.802A');
insert into #table2 values ('E07.9');


select from #table1;
drop table #table1
select
from #table2;
drop table #table2
96978-image.png


the table1 having value with comma seperated. i need to look this value into
table2 and update any single value into next column

i need the result table took like below


create table #tablewithunique (tbl1Data nvarchar(100), uniquevalue nvarchar(50));
insert into #tablewithunique values ('R07.9,R42','R42');
insert into #tablewithunique values ('E07.9,Z83.3,Z82.49','E07.9');
insert into #tablewithunique values ('R10.32','');
insert into #tablewithunique values ('S00.81XA,S59.802A,S13.9XXA','S00.81XA');

select * from #tablewithunique;
drop table #tablewithunique

97061-image.png


sql-server-generalsql-server-transact-sql
image.png (11.1 KiB)
image.png (6.8 KiB)
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

How is uniquevalue determined?
97035-image.png

For example: E07.9 and Z82.49 exist separately in the second table, why does the uniquevalue column show E07.9?

In addition, could you provide your sql server version?

0 Votes 0 ·
image.png (1.1 KiB)

Hi EchoLiu-msft,
Thanks for your reply.

I need either E07.9 or Z82.49. ultimately i need only single value.

Thanks

0 Votes 0 ·

SQL Server 2017

0 Votes 0 ·

Hi @kasimmohamed-6993,

Do you have any requirements for which value to display in the uniquevalue column?If you only need one value, then only one value that exists separately in the second table can only be returned randomly. Please refer to the answer.

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @kasimmohamed-6993,

Please refer to:

     ;WITH cte
     as(SELECT *
     FROM #table1 t1
     CROSS APPLY STRING_SPLIT(t1.tbl1Data, ',') v)
     ,cte2 as(SELECT c.tbl1Data,t2.tbl2Data as uniquevalue,ROW_NUMBER() OVER(PARTITION BY tbl1Data ORDER BY t2.tbl2Data DESC) rr
     FROM cte c
     LEFT JOIN #table2 t2 ON c.value=t2.tbl2Data)
        
     SELECT tbl1Data,uniquevalue FROM cte2
     WHERE rr<2

Output:
97064-image.png

If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.



image.png (5.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

kasimmohamed-6993 avatar image
0 Votes"
kasimmohamed-6993 answered EchoLiu-msft commented

Thanks a lot EchoLiu-msft .

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

You're welcome, I'm glad your problem has been solved!

0 Votes 0 ·