Add/Select mulitiple columns in SQL based on another column's value condition

Question

I am using SQL Server. Here is the scenario:

Let say I have a tableA:

UserId  UserName  TransactionType   TransactionDateTime
1       Staff1    NULL              NULL
2       Staff2    1                 2020-08-12 03:11:20.4871383
2       Staff2    2                 2020-08-12 03:41:33.4850314
3       Staff3    2                 2020-08-12 03:41:33.4848626
3       Staff3    1                 2020-08-12 03:11:20.4869688

And I would like to query the result like:

UserId  UserName  ClockInTime                     ClockOutTime
1       Staff1    NULL                            NULL
2       Staff2    2020-08-12 03:11:20.4871383     2020-08-12 03:41:33.4850314
3       Staff3    2020-08-12 03:11:20.4869688     2020-08-12 03:41:33.4848626

So the condition is if type is 1 then the transactiondatetime will be clockintime, if type is 2 then it will be clockouttime.

I tried to use 'case when':

Select UserId, UserName, 
case when TransactionType = 1 Then TransactionDateTime else null End as ClockInTime,
case when TransactionType = 2 Then TransactionDateTime else null End as ClockOutTime
From tableA

and in this way it will return 5 lines:

UserId  UserName    ClockInTime                  ClockOutTime
1       Staff1      NULL                         NULL
2       Staff2      2020-08-16 03:11:20.4871383  NULL
2       Staff2      NULL                         2020-08-16 03:41:33.4850314
3       Staff3      NULL                         2020-08-16 03:41:33.4848626
3       Staff3      2020-08-16 03:11:20.4869688  NULL

Any help is greatly appreciated!!

Please show us what you tried. – Dale K Aug 16 '20 at 06:39 — Dale K, Aug 16 '20 at 06:39

score 1 · Answer 1 · answered Aug 16 '20 at 11:33

You can use aggregation:

select UserId, UserName, 
       max(case when TransactionType = 1 Then TransactionDateTime End) as ClockInTime,
       max(case when TransactionType = 2 Then TransactionDateTime End) as ClockOutTime
from tableA
group by UserId, UserName;

This works for your sample data because each user has at most two rows. If your real data is more complicated, this will return only the largest value for each.

However, such situations can get quite complicated. You should ask a new question if this is what you really need. Be sure to include explanations of what to do when there are multiple/missing transactions of a given type -- or clearly explain why this is not possible.

James Cooke · Answer 2 · 2020-08-16T07:06:28.603

I think something like this would do it:

    SELECT a.UserId, 
           a.UserName,
           CASE WHEN a.TransactionType = 1 
                THEN a.TransactionDateTime 
                ELSE null END AS ClockInTime,
           b.ClockOutTime
    FROM TableA a
    LEFT JOIN ( 
    
    SELECT UserId, 
           UserName,
           TransactionDateTime AS ClockOutTime
    FROM TableA 
    WHERE TransactionType = 2
   ) b
   ON a.UserId = b.UserId

I've not tested this - just writing it directly, I think it should work though, certainly a sub-query with a join is what you want.

score 0 · Answer 3 · answered Aug 16 '20 at 12:56

The table could be left joined to itself. This approach could be useful (with additional date inequality) if the users clock in and clock out over and over.

Select UserId, UserName, ct1.TransactionDateTime ClockInTime, ct2.TransactionDateTime ClockOutTime
From tableA ta  left join tableA tb on ta.UserId=tb.UserId
where ta.TransactionType = 1 and tb.TransactionType = 2;

Add/Select mulitiple columns in SQL based on another column's value condition

3 Answers3