 Hello friends. Welcome back. My name is Suseel Singh and this is continuation of SQL Server intro questions and answers. So today I am going to discuss one more question. And this question is related to city and row number. Here we can understand the logic behind row number and city. So let's start. So here we have an input table and this table is related to phone number. You can see it contains 5 columns. First is ID then the phone type then primary then sequence and then the phone number. So here we have output table and in this output table we have 4 columns. First is ID. Second is business phone number. So business phone number is related to when type input table is B and then the home phone number when type is H and then cell phone number when type is C. So this is our output table and this is our input table. So we can see we have to pivot this input table to get the output table. But the condition is we want those phone numbers which primary is 1. So here we can see we have primary column and then the sequence columns. All logic behind the primary and sequence. And then we want to pivot. So there is 3 logics. First when primary value is 1 then we want that row as it is and we are not going to see another row related to the same type and ID. And then if the primary value is not equal to 1 then we will see the sequence number and pick the maximum sequence number for that row and we want display in our output. So simply we can see for the ID 1 we have only a single column. So this is for business phone number. So 1 and the number is 1. So ID is 1 and the business phone number value is 1 and rest is not available. Then move to the ID 2. In ID 2 we have business we sorry we have cell phone number and we have home phone number. Cell phone number we have a single row. So we will display that record as it is and the number would be 2. So here we can see business phone number is not available for the ID 2 and we have the home phone number and home phone number first is a cell phone number. So cell phone number value is 2 and here value is 2. So this is our output and move to the home phone number. We have 2 rows for the home phone number. First we will check the primary. So primary value is 1 we can find out from here. So we will display 3 in our home phone number. So in our output we can see and then move to the ID 3. In ID 3 we have all 3 columns. Business phone number, home phone number and cell phone number. So business phone number we have a single row. So 5 will be displayed as it is and then move to the home phone number and in home phone number we can see we have primary value is 1. Then this row number is 6 will display in our output and then move to the cell phone number. In cell phone number we can see we have 2 rows. First is where primary value is 0 and second we have primary value is 0. So primary value is 0 for these 2 rows. So we have to move the sequence and pick the max sequence number that is 2. We will display in our output 9. So we can see here. So this is our input and this is off. Let's move to SQL Server Management Studio to write the carry. So this is our SQL Server and I have already created this table which is phone master and we can execute this table. And this is our input data. So this input data and I have displayed in slide. So these 2 data are same. So now we write a query by which we want to display this output. Okay. So you know there is several steps to perform this task. So first is our row number. So try to understand one by one. So it's very easy to understand in row number. I am going to use partition by on the behalf of ID and primary type. So I am not going to discuss what is a row number if you want then go to my SQL Server tutorials and you can see from there. Now you can see I am going to use partition by on the behalf of ID and type. And then I am going to use order by in his primary column in decreasing order and then sequence number in decreasing order. And his primary value might be null. So we are going to use is null and if value is null then it will be replaced by zero. Then it will be sought in decreasing order. If one is available then it will come first then values come for zero and other. And second if the value is same for two rows then it will move to the sequence number and sort in sequencing order. So according to our requirement we can see if the value of primary is not null sorry not one then pick the max sequence number. So by using this condition we can pick we can execute this query and now we can see we want only those rows which row number value is one. So we can filter from here where row number is equals to one. So this is our intermediate input table. So this is our first step. Now moved to our second step. So in our second step I am going to use the pivot. Basically in our input table we can see we have to use these rows as a columns. So we have to use the pivot. So here we can see we have used case statement. So if you haven't watched my previous session please go and watch then you can very easily understand what is case statement and how it works. So here you can see this is our selected statement and we have used one more CTE. And inside this second CTE I am going to use this first CTE. So in this first CTE as we have displayed the row number one we are going to pick the values of row number one only. So then if the value of phone type is B then it will return the phone number for the business. Second if the value is H then it will return phone number for the home. Second is C then it will return the phone number for cell phone number. If I am going to execute this query then you can see it will return the result all these four columns which we want in our output. But the values are not perfect as we can see here we want IDs only once but here IDs are going to repeat more than once. So we have to use one more logic by which we can simplify it and provide output as we want. So I have already typed the query so copy from here and I am going to paste it here and now we can execute. Column name should be business phone number. So it is a business phone number now execute and now we can see the our output. So it seems very good as we want. So this output and this output is very good. So logic behind it I am going to use the group by on the behalf of ID and then I am going to put the maximum business number and then maximum home number and then the maximum cell number because you know there is two rows one value is null another value is something then it will pick the max value. Max value should be where we have some value null value is not going to reflect over there. So if this problem has been reduced by using max and we don't want to display null then we have to use is null and we replace the null value by Na. So this is our output as we want. So I hope you have understand this logic and it's very important. If you like this video please subscribe our channel for many more videos. If you need this script it will be all over on my Facebook page. You can go there and copy. Thank you.