 Hello, welcome to SSUnited, Susie's side and this is continuation of SQL Server interview questions and answers. So one of my friend has attended interview in PineLabs. So this question was asked over there. So let's start with the question. So here as we can see, we have input table that would have three columns. First is ID, then name and then their occurrence. So in the output, we just want only two columns ID and name. But according to their occurrence, values will be repeated. So as we can see ID and name for ID 1 and name is A, occurrence is 2. So in the output A is coming 2 times for ID 2 that is 3 times. So in the output, it is going to repeat as 3 times for 3 is 1 time and 4 is 2 time. So we are getting the output as their occurrence. So how we can write the query for getting this output? So let's move to SQL Server Management Studio where we will write the query for getting this output. So as I am using SQL 2017, you may have another version of SQL Server. So here we will use recursive CT to achieve the output. So first of all, I would like to execute the create table statement to create the table. So commands completed successfully. So table is created. Now I just want to insert data on the table. So we can execute the insert statement. So 4 rows affected. Now I just want to check the data. So we can write select a stick from table that is part 53 repeated values and here we are getting the same metadata as we seen in the slide. Now we just want to write the recursive CT for getting the output. So how we can write the recursive CT? So first of all I just need to use as CT and inside the CT I just want to use the same CT. So here we just want to get the ID then name and then one as level. So this is level that is our occurrence and after that I just want to use the union or and here we can use select from our CT. Let me call this as a and now we can use the inner join. So this is inner join with the same table that is part 53 repeat value. Let me call this as b and here we have on condition. So a dot ID should be equals to b dot ID and we need to check the occurrence. So a dot level that is our occurrence that should be less than b dot occurrence. So this is the query. So here we just need to get the ID from b now name from b table. Then we can use a dot level plus one as level. So this is the occurrence. Now we can select data from the CT. So select a stick from the CT and now we can execute and we can check the output. So here we are getting the output but output is not sorted. So how we can sort the data? So we can use order by clause so order by ID. Now I just want to execute and compare our output. So here as we can see a is coming two times, b is coming three times, c is coming one time and d is coming two times. Now we can compare with the slide. In the slide a is two times, b is three times, c is one time and d is two times. So we are getting same output as we are expecting. So here how it is working? So basically first time when we are going to execute this then it will be going to get all the data with level one. Then we have used union all after that we have used the recursive CT. So in this recursive CT we are checking if this level which is in the first query is less or equals to b dot occurrence so that is the total occurrence. So if this condition will be satisfied then we want output and here we are going to increase the level by one that is occurrence by one for each time. So as we can see for a we are having two. So here for a first time we will get a with level one. Second time here we can see this is a that is one then b occurrence is two. So condition is satisfied. So level should be increased by one and value will be repeated. So one one time then two. In the second execution then level should be two and that would be checked in the on clause that will not be satisfied. So this recursive CT will be terminated. So I hope you have understand how we can write the query for getting this output. 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 you can copy from there. Thank you so much for watching.