 Hello, welcome to SSUnitech. So, shall decide. And this is continuation of SQL Server interview questions and answers. So, today we gonna discuss about one more interview question that is related to how we can deal with logical combination of words. So, before going forward, if you haven't watched part 29 of this video series, so I would strongly recommend to watch that video where you can understand how we can get the comma separated values. So, let's start with our question. So, as we can see we have input table that contains two columns. First is ID, second is value. And in output we have two columns. First is ID and second is value. So as we can see in our input, first three rows contains Gaggan, Gaggan and Gaggan. Next three rows contains Deep, Deep, Deep. Then we are having Sync, Sync, Sync. So, we want to rotate these values in a single column by Gaggan, Deep, Sync, then Gaggan, Deep, Sync and then again Gaggan, Deep, Sync. So, how we can get the output from this input? So, let's move to SQL Server Management Studio to write the query for getting this output. So as I am using SQL 2017 you may have another version of SQL Server but query would be same for all the versions. So, first of all I would like to execute our first statement that is for creating the table. So, table has been created successfully. Next I just want to insert record on it. So, as we can see nine rows affected, so data have been inserted successfully on this table. Now we can execute our selected statement to check the data. So, we are having the data as we were seen in our slide. Now we can copy this data and what approach we will follow for that go to on Excel and here I want to paste our input table and here as we can see we are having ID and value. If we will generate a new column with row number and we will do the partition on the value and we want to generate 1, 2 and 3 then again 1, 2 and 3 and then 1, 2 and 3. As we seen we are getting the comma separated values in our last to last video. So, here we can get 1 then Gaggan then comma then Deep then again comma then Sing and for 2 and 3 we will get the same data. So, this time comma will be replaced by blank. So, by using this approach we will get our output. So, go to on SQL Server Management Studio to write the query for getting this output. So, here I want to generate a new column with row number and then as we have discussed we need to use partition by on the value then order by ID. Now I can execute and we are getting the data. Now we need to insert these records in a template table. So, first of all I would like to create a template table. So, create table TMP and here we want ID which is our integer value. Then we have value that is our string value. So, work at 100 and then for our last this is our row number with integer and we want to create a primary key on the ID. Then I need to execute this query to create the temporary table. So, temporary table has been created successfully. Now I need to insert a record on it. So, insert into our temporary table and we are getting data from this selected statement. So, as we can see 9 rows affected. Now we can copy and I want to use select as tick from our temporary table. So, here we need to row number. We need to use space separated value. So, I want to use stuff then select then we can specify space and we want to add with value from our temporary table. And then we can put an alias with I where I.RW should be equals to O.RW. And here we can specify for XML path. Here we want to replace extra space which we are getting at the starting of this sub query. So, we want to start from one. Then how many characters we want to replace? We want to replace only one character from which we want to replace. We want to replace by blank. Now I can close the bracket. Now I can put an alias with S. Now we can execute our selected statement. So, we are getting duplicate values. So, for that we can use distinct and now we can execute. So, we are getting the values as we were expected. So, as we can see in our slide we are getting same 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. Thank you so much.