Click here to Skip to main content
15,301,841 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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


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

What I have tried:

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


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
Posted
Updated 16-May-21 21:10pm
Comments
RedDk 22-May-21 22:48pm
   
What?

tbl1Data uniquevalue
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
R07.9,R42 R42
E07.9,Z83.3,Z82.49 E07.9
R10.32
S00.81XA,S59.802A,S13.9XXA S00.81XA

Haven't a clue as to what you want here.

1 solution

Basically, don't use comma separated values in your table: each row should be separate, with one value per column.
If you need multiple values per row, then use a third table which "links" the other two via foreign keys, and use a JOIN to combine them again.

SQL string handling is ... um ... poor at best, and what you are doing is making a rod for your own back by going this route. It's possible to do, but it's gawd awful messy, and it will need careful thinking about every time you try to use the data if you leave it as it is! Converting comma separated data in a column to rows for selection[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900