Comparing two numbers strings and getting out the unique values

Question

Here is a sample table I made to better illustrate my problem:

Create Table SampleTable(
    TableID int,
    NumberRow nvarchar(500)
)
Insert into SampleTable Values(1, '15,21,23,41,44,5,50,59,6,')
Insert into SampleTable Values(2, '10,24,29,41,5,50,59,6,73,')
Insert into SampleTable Values(3, '10,15,21,24,29,33,41,50,59,60,61,62,66,73,')
Insert into SampleTable Values(4, '10,15,21,24,28,33,37,41,44,5,50,6,60,61,62,66,')
Insert into SampleTable Values(5, '15,24,33,41,5,6,61,62,66,73,')

TableID  NumberRow
---------------------------------
1        15,21,23,41,44,5,50,59,6,
2        10,24,29,41,5,50,59,6,73,
3        10,15,21,24,29,33,41,50,59,60,61,62,66,73,
4        10,15,21,24,28,33,37,41,44,5,50,6,60,61,62,66,
5        15,24,33,41,5,6,61,62,66,73,

After that I wrote a self join query:

Select 
    t1.TableID AS ID1, 
    t2.TableID AS ID2,
    t1.NumberRow AS Numbers1,
    t2.NumberRow AS Numbers2
From SampleTable t1
inner join SampleTable t2
on t1.TableID = t2.TableID - 1
Order by t2.TableID asc

Which results in:

ID1 ID2   Numbers1                                         Numbers2
-------------------------------------------------------------------------------------
1   2     15,21,23,41,44,5,50,59,6,                        10,24,29,41,5,50,59,6,73,
2   3     10,24,29,41,5,50,59,6,73,                        10,15,21,24,29,33,41,50,59,60,61,62,66,73,
3   4     10,15,21,24,29,33,41,50,59,60,61,62,66,73,       10,15,21,24,28,33,37,41,44,5,50,6,60,61,62,66,
4   5     10,15,21,24,28,33,37,41,44,5,50,6,60,61,62,66,   15,24,33,41,5,6,61,62,66,73,

Now I want to make two columns that show a string of numbers that are unique to both of the columns (Numbers1 and Numbers2).

So far I haven't come up with any solutions. My other approach was to make numbers be in a column instead of a string, but I still couldn't figure out how I could resolve my problem.

Normalize the schema; Read ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad?r=SearchResults&s=1|192.7434) — sticky bit, Apr 21 '20 at 11:24
Good day Tertmartus, Your database design is VERY problematic and does not fits your needs. Yes, we can solve it and provide a solution, but it does not mean that you should use it. Tabular structure is built to store a single value in each cell in the table. Using multiple values with a separator can lead to vert poor performance since you will need to pare the value each time you use it and probably split it. You should really think about re-designing your database. — Ronen Ariely, Apr 21 '20 at 11:30
@RonenAriely This is merely a sample table. In the actual project, the number values are stored separately in a column. I made this table to store string values, because I thought it would be easier to get my point across. — Tertmartus, Apr 21 '20 at 11:36
So you should provide the original structure or you will get solution to a wrong question. You should post DDL+DML which present a scenario as close as possible to the production. — Ronen Ariely, Apr 21 '20 at 11:44

score 1 · Accepted Answer · answered Apr 21 '20 at 11:33

Maybe this query can be helpful but I agree with the design considerations about your table

select   DISTINCT value as NUMBER1 ,'' AS NUMBER2  from (
Select 
    t1.TableID AS ID1, 
    t2.TableID AS ID2,
    t1.NumberRow AS Numbers1,
    t2.NumberRow AS Numbers2
From SampleTable t1
inner join SampleTable t2
on t1.TableID = t2.TableID - 1
) as tmp_tbl
CROSS APPLY string_split(tmp_tbl.Numbers1,',')  

UNION ALL 


select DISTINCT '',  value from (
Select 
    t1.TableID AS ID1, 
    t2.TableID AS ID2,
    t1.NumberRow AS Numbers1,
    t2.NumberRow AS Numbers2
From SampleTable t1
inner join SampleTable t2
on t1.TableID = t2.TableID - 1
) as tmp_tbl
CROSS APPLY string_split(tmp_tbl.Numbers1,',')  



+---------+---------+
| NUMBER1 | NUMBER2 |
+---------+---------+
|         |         |
|      10 |         |
|      15 |         |
|      21 |         |
|      23 |         |
|      24 |         |
|      28 |         |
|      29 |         |
|      33 |         |
|      37 |         |
|      41 |         |
|      44 |         |
|       5 |         |
|      50 |         |
|      59 |         |
|       6 |         |
|      60 |         |
|      61 |         |
|      62 |         |
|      66 |         |
|      73 |         |
|         |         |
|         |      10 |
|         |      15 |
|         |      21 |
|         |      23 |
|         |      24 |
|         |      28 |
|         |      29 |
|         |      33 |
|         |      37 |
|         |      41 |
|         |      44 |
|         |       5 |
|         |      50 |
|         |      59 |
|         |       6 |
|         |      60 |
|         |      61 |
|         |      62 |
|         |      66 |
|         |      73 |
+---------+---------+

Comparing two numbers strings and getting out the unique values

1 Answers1