 Hello friends, welcome to SS Unitech. My name is Susheel Singh and this is continuation of SIKO Server interview questions and answers. So today I gonna discuss about Pivot Operator and Dynamic Pivot Operator. So first of all we need to discuss about the Pivot. So what is Pivot? Pivot is a SIKO Server operator that can be used to turn unique values from one columns to multiple columns in our output. Basically it's a rotation of table. So what it mean? Let's move to another slide where we will understand. So this is our slide as you can see we have input table and it contains five columns. First is ID, second is customer ID, then mobile and then date and then duration. In our output we can see we have only four columns. First is customer ID and then the mobile number and we can see it's available in our input. But next two columns are coming from date. We can see we can turn unique values from date column in our output. How we can achieve this output? So let's move to SIKO Server Management Studio to write the query and get the output. As I am using SIKO 2014 you may have another version of SIKO Server. But query would be same for all the versions. First of all we need to create the table. So I gonna create the table. Table has been created successfully. And then I wanna execute insert statement and insert some record on it. So three are affected and we can select and we can check. So data is available as I have displayed in our slide. First of all we need to write the pivot operator. So for using pivot operator we have to specify pivot and then open bracket. And inside the bracket we have to use that column by which we want to aggregate the values. So we want to sum of these durations. Then for which column we want to turn the unique values we have to specify over here. So called date is our column. And then we have to use in operator then again open the bracket. And inside the bracket we have to specify the values. So here we can see we have two unique values. First is 24, 3, 18 and second 25, 3, 18. So we have to specify over here. So 2018, 3, 24 and then 2018, 3, 25. We have to specify in our selected statement as well. We want to get four columns. First is customer ID and then the mobile number. So we can see mobile number, customer ID and then two columns. And we have 24. So we have to specify 24. And now we can execute. We are getting some error. So we have to specify alias name for this pivot operator as I am using pivot. Now we can execute and we can see. So here we can see it's not going to return as we want in our slide. So in our slide we can see we have only one row by which we are getting exact same output. But here we are getting three rows. So why it's not going to return as same output as we want? Because in our table we have an ID column and this ID column is not used in our output. If we don't want to use ID column in our output, so we have to remove this column from our selected statement. So we have to select only those values which are going to use in our pivot operator. So we have to use only four columns. So first is customer ID, then the mobile and then the call date and then the duration. So we have to use duration. And then we have to set in a derived table and we have to put an alias name for this derived table. And we can execute this query. And now we can compare between our slide and in our output. So here we are getting same output as we want. So I hope you have understand how we can use pivot operator. If we have a scenario by which date column values is going to increase on daily basis, then how we can write a query by which there is no need to put changes on daily basis. So let's move to other slide where we will discuss. So here we can see we have input table and it contains six columns. First is customer ID, then the customer name, then the mobile number, call date, start time. Then the duration of that call. And in our output we can see customer ID name and mobile number is also present in our input table. But we want to turn the unique values from call date column in our input to our output. In our output we have 2318 and 21318, 22318. And in input table we can see we have 20, 21 and 22 these three unique values in call date column. So how we can write a dynamic pivot by which we can achieve this output. So let's move to SQL Server Management Studio where we will discuss how we can write the query. Now I want to execute this creative statement table has been created successfully. Then I want to insert some record on it. So we can execute this query and here six rows have been inserted in our table. So we can execute and we can compare between slide. So these metadata are same. So first of all we need to put unique values from this table to a temporary table. So we can choose distinct keyword and then call date into our stamp. So unique values will be inserted in our table. So now we have to declare a variable declare at the rate. This is the column name. So we have to use at the rate call and this is backer max. Here we can see we have to use a loop by which we can create the column dynamically. So we have to use while exist. So I am going to check the values whether these values are present in our temporary table. Then this loop will be executed and here we can and so here we need to select the first row. So we have to use top operator top one and then from this temporary table and we need to assign in our column variable. So column variable is equals to at the rate column. Then we need to concatenate. So first of all we have to specify our bracket. Then we have to cast and then called it is our column name as wirecare 20. And then again we need to close and then put comma. So it's going to set the values in our column variable and then we need to delete top one from temporary table. So it will delete our first row and now I want to select and we can check. So table has been created already. So we need to so here it's going to return null value because we have to specify blank at the starting. But at this time we don't have any value in this temporary table. So we have to drop and recreate and then we need to execute and here we can execute and we can see it's going to return the value. But here we can see we have extra comma. So we have to remove that comma. So we need to select values from left of this variable call and then we need to check the length and we need to subtract one and it will set the value. So we can execute and we can see comma has been eliminated. So we have to select as well. So now we can execute and we can check. So here we can see our column is created successfully. So next we need to write a dynamic SQL by which we can put the pure operator. We need to declare one more variable. We can call as SQL and then worker max. First of all we need to in slice as blank. So first we know how we can write pure operator. So we can write select from then our table name. So what is our table name dynamic pure table is our table name. So we have to specify over here. So this is our table and in our select statement we need to select only three columns as we have displayed in our slide. First is customer ID. So this is customer ID then the name and then the mobile number. So these three columns are going to select over here and then we need to concatenate. So we have to use plus and then add the red SQL. So this is our dynamically created columns. As we have discussed we have to select only those rows which are going to used in our pure operator. So we need to eliminate those rows which are much part of pure operator. So we have to use all these three columns and then the call date and then we have one more column which is duration. So duration. So these columns are required. So we have to use only these columns in our selected statement. So here we can see we have done almost. So we need to use pivot and then brackets. And here we need to put some of duration and then we have to use for and we have to specify call date and then in we have to use bracket. And inside the bracket we have to use our column. So this is our columns and here we need to use an ideas name. So I am going to put these values in our dynamics SQL. So this is and then we need to put plus sign over here and over here and space and then command and plus then again plus plus here. We need to set and then again plus sign and then I am going to set these values in our SQL variable select and we can check what values are present in our SQL variable. So here we can see we have this statement we can copy we can paste we are not getting the column names over here. We have to use at the rate column. Now I am going to execute and we can copy paste it over here and now I am going to execute as we can see we are going to get the output. So first of all we have to use execute this dynamics SQL and then we can see now we can see we are going to get the output. I hope you have understood how we can get the output by doing dynamic rate operator. Thank you so much for watching this video. If you like this video please subscribe our channel for many more videos and press bell icon to get all the notifications for new videos. If you need these scripts it will be available on my Facebook page you can copy from there. Thank you so much for watching.