CODEWITHSUNDEEP
sqlsql-servert-sql
Raymond
Raymond

Reputation: 572

tsql convert rows into column without grouping

This is table schema,

CREATE TABLE [LOGIN]
(
    [USER_ID] [INT] NOT NULL,
    [ACTION] [VARCHAR](50) NULL,
    [MODIFIED_DATE] [DATETIME] NOT NULL,
    [IS_SUCCESS] [BIT] NOT NULL
) ON [PRIMARY]
GO

This is the result from my query:

USER_ID    ACTION      MODIFIED_DATE             IS_SUCCESS
-------    ------      -----------------         -----------
0001       Log In      2018-06-01 13:44:02       1
0001       Log Out     2018-05-31 22:56:21       0
0001       Log In      2018-05-31 12:27:40       1
0001       Log Out     2018-05-30 23:43:05       0
0001       Log In      2018-05-30 12:28:08       1
0001       Log In      2018-05-30 12:28:07       1

And I want something like this:

USER_ID   Log In                   Log Out
-------   -------------------      -------------------
0001      2018-06-01 13:44:02      NULL
0001      2018-05-31 12:27:40      2018-05-31 22:56:21
0001      2018-05-30 12:28:08      2018-05-30 23:43:05
0001      2018-05-30 12:28:07      NULL

Is this possible? I've tried other posts from Stackoverflow but mostly are counting or using group. I don't want to group.

Upvotes: 0

Views: 52

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You can use lead():

select l.user_id, l.modified_date as log_in,
       (case when next_a = 'Log out' then next_md end) as log_out
from (select l.*,
             lead(modified_date) over (partition by user_id order by modified_date) as next_md,
             lead(action) over (partition by user_id order by modified_date) as next_a
      from login l
     ) l
where l.action = 'Log in';

This looks at the next record, remembering the date and action for it. It then filters down to just the logins and uses logic to select the right values when the next row is a logout.

Upvotes: 1

kiran gadhe
kiran gadhe

Reputation: 743

You can do this with simple case statement.

SELECT  USER_ID , 
(CASE WHEN Action= 'Log In' AND  IS_SUCCESS= 1 then MODIFIED_DATE ELSE  NULL END ) AS 
LOG_IN,
(CASE WHEN Action= 'Log Out' AND  IS_SUCCESS= 1 then MODIFIED_DATE ELSE  NULL END ) AS 
LOG_OUT,  
FROM [LOGIN]

Upvotes: 0

Related Questions