 Hey guys, welcome to SSUniTek, Sushil this side and this is continuation of Cipro server real-time scenarios. So today we are going to discuss about very interesting function which was introduced in 2K17 and this is string underscore AWG. It means string aggregate function. If you haven't watched part 2 of Cipro server interview questions and answers video series then I would strongly recommend to watch that video before going forward. So I will provide the link of that video on the description of this video. So you can directly click on that link and watch that video because on that video I have explained how we can achieve the string underscore aggregate offset inside the Cipro server without using this function. So go to on the next slide and we will see what string underscore aggregate function does. So basically how we can get the comma separated value or any another separated values inside the Cipro server. So for that we could directly use the string underscore aggregate function. So here for the sample we have an input table that would have 2 columns id and name. In the id column we could see we are having id 1, 1, 2 times and name is cd and ab. So in the output as you can see id is coming one time but if name are coming more than one times then we could add those by using the comma. So in the first result set we could see cd comma ab and in the second result set we could see ab comma cd. So by using this function we are also having the option to do the sorting inside the data. So data is our column which is comma separated value. So directly how we can achieve that option we can use string underscore aggregate function and get the output. So go to on the Cipro server management studio and where we will see in the practical. So here basically I have just written to create a statement and inserting the data on this table and selecting that. So let me try to execute this query. So this basically going to create the table and inserting the data and selecting that data. So this is the same data as we have already seen inside the slide. So now the first thing that how we can use the string underscore aggregate function. So this is very straightforward. So instead of using the as stick we can write the id column which will be our first column. In the second column we need to use the string underscore aggregate. So this is the string underscore aggregate function. Let me open the bracket. Then we need to specify the name of the column. So our name of the column is name. Then we can put the comma and the second parameter will be the separator. So here we are having the comma separator. We can close the bracket and we can call this as data. So everything looks good. So here we are going to do the aggregate on this name. So obviously we need to use the group by clause on the other columns. So the column is id in this case. Now let me try to execute this query. So here we are getting the output as we are expecting in the first result set as we can see inside the slide. So what it is going to do? It is going to pick the first value and checking for another value. If it will be going to get that value then adding that value after that. Like cd is coming first time. So cd then put comma and after that if it found any another value then it is going to add with the a b. So this is the first one. Now the second how we can achieve that second output. So this is again very straightforward. Let me try to complete this query and try to paste that query below that. So basically in this query we need to do little bit modification not very much. So all this query will be same. But after this aggregate function we need to use the within group. So we can directly write the within and after that we need to write the group. Then we need to start the bracket and close the bracket and inside this bracket we need to specify the order by clause. So order by on the name. So it looks good. Now we can directly try to execute both these queries. So once we will execute then we could see this data is going to sorted in this group. So for example one so previously it was cd and a b and according to the values it is sorted on that particular row. So I hope guys you have understand how we can use the string underscore aggregate function. So I will try to put this code in the description of this video. So you can directly copy and do the practice on your own. If you still have any doubt or if you have any other questions so you can directly comment on the comment box. So thank you so much for watching this video. See you in the next video.