 Hello, welcome to SSUnited. So, see this side and this is continuation of SQL Server interview questions and answers. So, today we are going to discuss one more interview question and this is very tricky question. So, characters occurrence in a column. So, what is this question? So, move to next slide to understand about the question. So, here as we can see we have input table and it contains two columns. First is ID and second is name. In the output we are having only two columns. First is character and second is their occurrence. So, here as we can see in the name we are having Joanne, Howard, Rosie and John. So, we have total four rows. So, J is coming two times. First in the Joanne and second in the John. So, in the output as we can see for J occurrence is two. Second for O. So, O is coming one time in Joanne, one time in Howard, one time is Rosie and one time is John. So, total O would be four. So, occurrence is four. So, how we can get this output from this input? So, let's move to SQL Server management studio where we will write the query and get the output. So, as I am using SQL 2.0.12, you may have another version of SQL Server. So, first of all I would like to create the table. So, we can execute this query. So, query is executed successfully. Now, here I just want to insert data on the table. So, we can execute this insert statement. Now, I just want to check the data. So, we can execute this selected statement. So, we are having the same metadata as we seen in the slide. So, here I just want to create this output table that would have character and their occurrence. So, character will be second column and total will be the occurrence. So, we can execute this create table statement. So, table is created. Now, our logic will be started. So, here first I need to check how many number of rows we have and then we need to pull one by one and loop through their strings. So, here I need to declare few variables. So, first of all I would like to declare first variable that is i and data type is integer and value is 0. Next, I want to declare one more variable that is k and it would have where care and this is 100. So, next variable I just want to declare for the length. So, adred l data type is integer and value is 0. So, I have declared 3 variables and I just want to put value in the adred i variable. So, I just want to get the number of rows. Select adred i is equals to here I just want to use the count of one from our input table. So, that is string iq table. So, next I just want to declare one more table. So, declare adred j and this is a table and it would have only one column that is name. Data type will be where care and value is 1. So, we have done with the declaration of variables. Now, I just want to use a loop. So, while this adred i is greater than 0, this loop will be executed. So, we can use the begin then and so inside this begin and we need to implement our logic. So, first I just want to get the name one by one into adred k variable. So, I can use select then adred k is equals to name from here we can use our table that is a string iq table where id should be equals to adred i and here we need to take care one more thing like in the name we should use L trim and R trim. If we will have any blank spaces then those blank spaces will be removed. Next I need to use one more loop. So, while adred k value is greater than 0 then this loop will be executed. So, begin then and in between we can apply our logic. Next, I just want to insert data into our adred j table and we want select left adred k comma one. So, we just want insert only single character from the adred k variable. Then here we want to use the merge statement. So, merge then our output table. So, let me copy this. This is our target table. So, let me call this as t then using and here we need to use adred j table. Let me call this as source table. Then we need to specify on condition. So, t dot character. So, this is character should be equals to s dot name. Now, when it gonna match then what we want to do? So, if matched then I just want to update. So, we can use the update then set total should be equals to total plus one. Next, when not matched by target it means data is not available in the output table. So, then we just want to insert data. So, insert and character comma total and values will be coming from source. So, it should be s dot name comma one because it is first occurrence. So, we have done our logic. Now, we need to use semicolon to terminate this merge statement. So, we have done the merge statement. Now, we just want to use the set adred k value right and here adred k comma length of adred k minus one. So, this value will be in slides in the adred k and here we again need to check if we have any blank space then that blank space should be removed. So, we can use L trim and R trim. So, L trim and R trim. Again, we need to use L trim and R trim. After this, we just need to delete data from adred j table. Now, we have done our inner loop and outside this loop we have one more loop. So, here set adred i equals to adred i minus one. Now, we have done the logic. Now, let me execute this and check the output. Initially, in the output table we don't have any data. So, let me copy it and just I want to check it. Select a stick from table. Now, execute this. We don't have any data. Now, I just want to execute this query. So, here I made a mistake. Here we can't directly check. Here we need to check the length of this adred k. If length of this adred k is greater than zero then we want in the output. Now, let me execute this and here as we can see this is executed successfully. Now, we can execute and check the output. So, here as we can see j is coming two times, o is coming four times, then h is coming two times. So, we can compare and we are getting the same output as we are expecting. So, we have total 11 rows and we are getting their occurrences. So, this query is very simple if we can understand established. So, here first I have declared few variables those are going to use in the query. So, in the query first I have checked like if we have four rows or five rows or n number of rows. So, it will be going to execute this loop for all the rows. Then we need to fetch data from the source table one by one and name will be setting in the adred k variable and here if the length of this adred k variable is greater than zero then we want to execute our inner loop and inside this loop I just want to insert data on this temporary table that is stable variable and we just want only one character from this adred k. Then I just want to use the merge statement with the output table using adred j table where I have put the only single characters. Then here we have on condition if value is matched then we just want to increase this total value that is occurrence by one. If not matched then we just insert with occurrence one and here we have fetch out all the characters except left one character. Then delete data from this adred j table and here we have decreased the value of this adred i which is the total number of rows. So, I hope you have understand how we can write the query and we can get this output. So, this is very tricky question you can understand step by step so that would very easy. So, thank you so much for watching this video if you like this video please subscribe our channel to get many more videos don't forget to press the bell icon to get the notification of our newly uploaded videos and if you need this script it will be available on the description of this video. So, thank you so much for watching.