Select Records From Vertical Row And Transform Horizontal Row

I am working on a SQL Server query to fetch the last Badge_In_Out_Time of the list of users who will be working on multiple task codes. task_mgmt - tablename Table Columns as follo

Solution 1:

Just do a simple pivot by aggregating your rows to the desired level (UserName,TaskCode) and then pivoting using MAX or MIN(CASE WHEN .... END)

Like so:

SELECT 
    tm.UserName
    ,tm.Task_Code
    ,MIN(CASEWHEN tm.Action ='START'THEN tm.Badge_IN_OUT_TIME ELSENULLEND) AS Badge_IN_TIME
    ,MAX(CASEWHEN tm.Action ='END'THEN tm.Badge_IN_OUT_TIME ELSENULLEND) AS Badge_OUT_TIME 
FROM 
    task_mgmt AS tm 
GROUPBY 
    tm.UserName
    ,tm.Task_Code

Solution 2:

This is a type of gaps-and-islands problem, the islands are pairs of START and END.

There are a number of solutions. Here, I have assumed that START always defines the beginning of each group, and there may or may not be an END.

We can calculate a grouping number for each island by using a windowed COUNT, partitioning by UserName and Task_Code. We then simply group up also by the new grouping number, and pivot the timestamp.

SELECT
  t.UserName,
  t.Task_Code
  MIN(CASEWHEN t.Action ='START'THEN t.Badge_IN_OUT_TIME END) Badge_IN_TIME,
  MAX(CASEWHEN t.Action ='END'THEN t.Badge_IN_OUT_TIME END) Badge_OUT_TIME
FROM (
    SELECT*,
        COUNT(CASEWHEN t.Action ='START'THEN1END) OVER
           (PARTITIONBY t.UserName, t.Task_Code
            ORDERBY t.Badge_IN_OUT_TIME ROWS UNBOUNDED PRECEDING) grp
    FROM task_mgmt t
) t
GROUPBY
  t.UserName,
  t.Task_Code
  t.grp;