 Hello friends, welcome to SSUnited. My name is Susheel Singh and this is continuation of SQL Server interview questions and answers. So today we are going to discuss about what are the real time use of unpivot operator. When interviewer ask such type of question to us then they want to know have we used these objects in our real time scenario or not. So here I have created a scenario if interviewer ask this question to you then you can explain this scenario to interviewer. So let's move to another slide where I have created a scenario. So here we can see we have two input tables. First table contains the information of location and second table contains the information of invoice. So in our first table we can see we have column 1 which is location and this is our form location and rest of the columns contains the information of two locations. So here we can see when we want to deliver any item from location Mumbai to location again Mumbai then it doesn't take any time. So if any customer purchase any item on the same day it would be delivered. If item would be delivered from Mumbai to Bangalore then we can see it takes three days. In our second table it contains the information of invoice as you can see we have invoice number customer ID date from location to location and in our output we can see invoice number customer date from location and to location is same. But we want to calculate the expected delivery date would be calculated from location table. But here we cannot apply join between these two tables because location table doesn't contains the data in normalized form. So how we can get the normalized data. So we have to rotate our columns to our rows. So it would be very helpful by using join we can get the output. So how we can do the same. So let's move to another slide. So this is our slide by using unpivot operator we can rotate the column values to our row values. As we can see we have from location and then to location. To location comes from columns like Mumbai is our from location then to location is Mumbai and then second column is Bangalore then Bangalore then third Delhi so Delhi Lucknow then Kolkata so it's Lucknow and Kolkata and value is 0 3 as we can see in our input table. Once we will get the data from this format then we can simply apply join between invoice table and this unpivot output table then we can simply calculate expected delivery date. So how we can rotate this table to normalized form. So let's move to SQL Server Management Studio as I am using SQL 2014 you may have another version of SQL Server but query would be same for all the versions. So here I have already typed the query to create the table. So first I gonna create location information table. So this is location information table so table created successfully and then I wanna insert some record 3 rows affected then I gonna create invoice information table. So execute this statement table has been created successfully and then we gonna insert record on it. So we can see 4 rows affected as we can see in our slide. So now we have location information table and we want to rotate this location information table. So how we can rotate select star from our table as we can see it contains the information as we have explained in our slide. So we have to use unpivot as we are using pivot operator in our previous slide. If you haven't seen that video I would strongly recommend to watch that video. So it would be very helpful to be understand. So first of all we need to write unpivot then we have to specify the value. So value column should be number of days and for what we want to get this number of days so we have to specify 2 location. So this is our 2 location. Then we have to use in operator and then we have to specify the column name. So first is Mumbai and second is Bangalore and third is Delhi then Lucknow and then Kolkata. So we have to specify unpivot. So I am using an alias UN. Now I am going to execute this query. Once I would execute this query then we can see we are getting location and then number of days then 2 location and in our slide we can see. So from location to location and then the value. So this is our value column. So we are getting the output. Now I am going to insert in a temporary table into as temp so 15 was affected. Now we need to apply join between invoice table and this unpivot output table. So select as tick from this is our invoice table. Now we need to apply left join because you want to get all the information from invoice table. Doesn't matter it would be a label on this temporary table or not. So this is our temporary table I am going to put an alias as T and this is our invoice table I want to put an alias with I and then I dot this is our from location should be equals to T dot this is from location and then I dot this is our to location should be equals to T dot to location. So I want to execute this selected statement. Once this selected statement would be executed then we can see number of days are coming over here from location to location all the information are coming. But we don't want all the information we want all the information from invoice table and just want to get the number of days values from this temporary table so T dot number of days. Now we can execute and we can see number of days are coming but we want to get expected delivery date. So we need to use date add function then we need to add days then increment values should be number of days then we want to add this date and now we can execute and we can see it's coming expected delivery date expected delivery date now we can execute and we can see our output and we can compare between slide and our output as we have achieved. So here we can see our advice number I1 expected delivery date is 17 so it's same and second 21 and then 32 and then again 22. So we are getting same output as we have explained in our slide. So I hope you have understand how we can get this output by using unpivot operator. So if you like this video please subscribe our channel to get many more videos and press bell icon to get notifications for new updates. If you need this script it would be available on my Facebook page. You can copy from there. Thank you so much for watching.