 Hey guys welcome back my name is Susheel Singh and this is continuation of SQL Server interview questions and answers. So today I am going to discuss one more question which is related to interviews and this question is totally related to attendance. So let's move to another slide. So in our second slide you can see we have an input table and it contains four columns. First column is employee ID, second is employee name and third is working day and fourth is attendance. So any employee which is working in any organization is work for any day. So here you can see employee ID one, employee name is join and working day is 15, 3 and 18. So on this day join attendance is present for another day where is 16, 3 and 18 and join is also present. And for the 17th, 3 and 18 you can see join is absent for another employee which is Howard. So 15, 3, 18 Howard was present but 16 and 17 Howard was absent. So you can say we want output where it has four columns, first is employee ID, second is employee name and third is present and another is absent. So we want to split these attendance in rows two columns. So how we can achieve this output by using SQL Server. So let's move to SQL Server Management Studio where we will understand how we can achieve this output. So this is our SQL Server Management Studio and I am using SQL Server 2014. So here I have already typed the query so it will save the time. So here I have written create table and table name is employee attendance tbl and then it has four columns as I have discussed in slide. So it has primary key which is composite primary key and it is on employee ID and working day. So here you can see we can directly execute this query to create the table. Once we have executed this query table has been created successfully. So now I am going to insert the record on this table. So we have already typed the query. So we can directly select and we can see the output. So this is our output. We can match this output with our slide. So this input table and this output is same. So we can directly insert to our table. So we can execute this query. This query has been executed successfully and six row affected. So six row has been inserted into our table. Now we can select and we can see this is our table and table has been created successfully with input data. Now we need to write the query by which we will get the appropriate result. So we need to write select then from and this is our table. So we have to write the table name. So we can copy from there and we can paste it here. So now we want only four columns. First is employee ID, second is employee name. So we can simply write EMP ID as it is and then EMP name. And then we want present and absent. So here this is our input table and in input table you can see present and absent. Both are present in rows. So we need to split these rows in our columns. So how we can do? We have to use the case. So now case and then when now I am going to check where attendance is equals to present. So if this value is present then I am going to return one else I am going to result zero. So this is our end. So as we can say this is our present. So we can write the alias name for the same and this is present. Now we can execute this query and we can see it's going to we execute these queries at a single time so we can compare between them. So this is employee ID one where employee name and the present. So present is one. So we can see for join we have to present. So here it's going to result to present. One absent so it will return zero. So we can copy this case statement and execute this case statement for the absent. And here we can remove this present from absent. So and we also need to remove this alias name from present to absent. So we can do this. And now we can execute this query and we will get the result. Now we don't want to see the data as we have displayed here. We want to summarize the data. So we have to use group by and we can use the sum for these present and as well as absent. So we have to write some for these two. And now we have to use group by on the behalf of employee ID and employee name. So we need to write group by and employee ID and employee name. Now we can execute and here we can see we are going to get the result as I have displayed in slide. So this is our slide. We are getting employee ID one which is joined and present is two and absent is one. So in our output we can see join present is two, absent is one. So data has been matched. Now we need to write the order by. So we have to write order by on the behalf of employee ID. So we will get the same result as I have displayed. So I hope you have understand how we can achieve this output by using SQL server. So if you like this video please subscribe our channel for many more videos. If you need this script it will available on my Facebook page. You can copy from there. Thank you so much for watching.