 Hello guys, welcome back. My name is Sushil Singh and this is continuation of SQL slower interview questions and answers. In this session, I am going to discuss how we can apply join in a single cell. So let's move to our question. This is our question. We have two input tables. First is item master and second is variant master. In item master, we have two columns. First is item code and second is variant ID. In variant ID, you can see you don't have normalized data. So you have comma separated values in variant ID. In variant master, we have normalized data and it has only two columns. First is variant ID and second is variant description. In our output, we want to put join between variant ID of item master and variant ID of variant master. And then we want to display our output. In our output, we can see simply we have to pick the values for item code and then we will pick the value for variant description from variant master. So here we can see variant ID one. So variant ID one, variant description is for variant ID one is a. So we have to display a as it is. And second is two. So value for two is B. So B will be displayed as it is. So for item code two, variant ID values is three comma four. So three is C and four is D. So for two C comma D will be our output. Now let's move to SQL Server Management Studio where I have already created these tables. So our first table is item master and it has same metadata as I have displayed in slide. So this metadata and this metadata both are same and I have created second table as well which is variant master and we can see it has the same metadata. And before going forward I would like to explain about the care index, how care index work. So care index will return the position of any character with a within a string. So our input string is to seal comma sing and we won't get the position of comma. So we can simply execute and we can see it will return the first occurrence of this comma of this comma. When we are going to execute, then we can see it will return it. So we can count from S. So we can see comma is in eighth position. So it will return it inside the query. So you can see this is our query by which we can get our output. So this is our output. And before going forward, I would like to select item master. So in item master first of all I want to split variant ID one and then two. We want to split these IDs in separate column and these ID would be separated by using this comma. So here inside the city I have already write the code by which we can see it will return our desired output. So here you can see in our first left value from this comma, we are going to use the left function and inside the left function, we want to pick the value from this variant ID column. And then how many characters we want, we want to pick only those characters, which is before this comma. So comma will be written by using this care index as I have explained earlier. So it will return the exact position where comma exists. Then I have to subtract minus one by this care index value because it will return value where comma exists. So we want value before comma. So it will return the value for left joint. And second, I am going to use sub string. So in this sub string, we are going to select value from this variant ID column. And we want to pick the value where this comma will exist. It will return our first occurrence of this comma. And we want to pick the value just after this comma. So we have to add one from this care index. And then how many characters we want. Then we can calculate the total length of this cell. And then we have to subtract the comma value. So we can simply execute our length function. Then we can subtract comma. Then we will get our result. So this is our result. We can execute and we can see this is our desired output. So we have to simply put joint between this table with our variant master table. And then we are going to command the output for these two tables. So here you can see I am going to use left joint between this CT and this variant master table on the behalf of first of all left joint column and second for right joint column. So first we will return the values for before this comma and second we will return after the comma. So once I am going to execute, then we can see it will return our desired output as we have seen here. So both outputs are same. You can use the query and we can get the result. I hope you have enjoyed this video. If you like this video please subscribe our channel for many more videos. If you need these scripts will available on my Facebook page. You can copy from there. Thank you so much for watching this video.