 Hello, welcome to SSUnitech. So, see this side. And this is continuation of SQL Server interview questions and answers. So today we gonna discuss about how we can get the comma separated values. And this is very important interview question. Generally interviewer ask this question to us. In real time you may have faced this scenario many times. So, let's start with our question. So, as you can see we have input table that contains six columns. First is ID, then the training name, classroom, start time duration and weekdays. And in our output we want five columns. First is training name, then classroom, start time duration and weekday. In our weekday as we can see we are getting the comma separated values. So, as we can see in our SQL Server. So, we have total four rows. So, our first four rows see we have Monday, Tuesday, Wednesday and Friday. So, we are getting four days. In our output these days are coming in a single column with comma separated values. And for MSBI as we can see our last three rows. Then all the values are same except ID and weekdays. But ID we don't want in our output. We just want only weekday in our output. So, we will get Monday, Thursday and Friday in our output by comma separated values. So, let's move to SQL Server Management Studio where we will write the query to get the output. So, as I am using SQL 2014 you may have another version of SQL Server but query would be same for all the versions. So, this is our query for creating the table. So, I am going to execute this query to create the table. Command completed successfully. So, table has been created. Now, I am going to insert record on it. So, we can insert records. So, as we can see seven rows affected. So, records have been inserted successfully. Then we need to check the record. So, as we can see we are getting seven rows as we have seen in our slide. So, before going forward let's discuss about stuff and for XML path. Basically, stuff is used for the masking. As we can see I want to create a scenario by which we will use stuff. So, this is our stuff. Then here we need to specify the expression by which we want to apply masking. So, I want to use ABC at the rate ABC. Then we need to specify the starting position. So, I want to start from one. Then how many characters? We want two characters. So, AB will be masked by. Here we need to specify the expression value by which we want to apply mask. So, I just want to use star. I want to execute or select a statement. Then we can see AB has been removed and we got the value as star. So, this is for the stuff. Next, I want to discuss about for XML path. So, what for XML path does? So, let's select this table. So, select star from our comma separated table. And we want to apply comma separated values for weekday. Then we want to add comma. Here we need to specify for XML path. So, I want to use for XML then path. Here we need to specify bracket. Then I want to execute our selected statement for XML path. So, here as we can see we are getting the value in XML. So, we are getting all the values by comma separated. But here we can see we are getting one extra comma. So, by using star function, we will replace this comma. Here I want to put in a drive table. So, this is our drive table and I want to put an alias with a. Then select. I want to use stuff. Then we need to start the bracket. So, this is the expression where we want to replace our first comma by blank. So, we need to specify comma. Then starting position should be one. Then again comma. Then how many characters we want? So, we want to replace only one character. Then here we need to specify the replaced value. So, that is blank. Now, I want to execute this selected statement. So, as we can see we are getting the comma separated values as we were expected. So, this is our for XML path. Now, I want to write the query to get out. So, select star form table name comma separated value. So, here as we can see we are getting all the columns. But in our output, we don't want all the column. We just want training. Then classroom. Then start time. Then duration. And then we want comma separated value for widgets. So, here I want to use this for XML path. So, I can copy and paste that value over here. So, here we need to specify our brackets. So, this is our subquery. So, this time once we will execute this, then we can see we are getting all the values by comma separated. But we just want to comma separated values where the training for our inner query would be equals to our outer query. Here we can use the alias with a for our outer query. I want to use alias with b. And here we can apply the condition where a dot training should be equals to b dot training. Now, I am going to execute our selected statement. So, as we can see we are getting the comma separated values. But the data is going to be repeated. So, we don't want to repeated data. So, we can use distinct. Now, I want to execute our selected statement which we have created. So, here we are getting the values. So, MSBI as equals. Then classroom. Start time. Duration. Then in our last column, we are getting the weekdays by comma separated values. So, thank you so much for watching this video. If you like this video, please subscribe our channel to get many more videos. If you need this script, it will be on my Facebook page. You can copy from there. Thank you so much for watching.