Changing Rows to Column in SQL

Question

I have the following Data:

CREATE TABLE TimeLog (
    [User] NVARCHAR(6),
    [Event] NVARCHAR(3),
    [Time] DATETIME
);

INSERT INTO TimeLog VALUES
    (N'Jibran',N'IN','2015-04-15 00:31:00'),
    (N'Jibran',N'IN','2015-04-16 20:10:00'),
    (N'Jibran',N'IN','2015-04-21 14:59:00'),
    (N'Jibran',N'OUT','2015-04-22 01:01:00'),
    (N'Jibran',N'IN','2015-04-22 10:46:00'),
    (N'Jibran',N'OUT','2015-04-23 00:58:00'),
    (N'Jibran',N'IN','2015-04-23 14:50:00'),
    (N'Jibran',N'OUT','2015-04-24 01:37:00')

I want Each Event(For Every IN/OUT) to have a new Column with the User and Time as Rows.

Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Andrea, Oct 19 '18 at 08:50

score 0 · Answer 1 · answered Oct 19 '18 at 05:47

0

use aggregate function with case

select user, max(case when Event='IN' then DATETIME end) as IN_time,
max(case when Event='OUT' then DATETIME end) as Out_time from t
group by user

answered Oct 19 '18 at 05:47

Zaynul Abadin Tuhin

31,407
5
33
63

score 0 · Answer 2 · answered Oct 19 '18 at 07:20

I am assuming you are having consecutive IN and OUT events for each user, order by date correctly (not like in your example data exactly).

You can use ROW_NUMBER to group the IN and OUT event in pairs, like this:

SELECT ROW_NUMBER() OVER (PARTITION BY [User] ORDER BY [Time])  + ROW_NUMBER() OVER (PARTITION BY [User] ORDER BY [Time]) % 2 AS [PairID]
      ,*
FROM [dbo].[TimeLog];

and then PIVOT to get the following result:

SELECT [User]
      ,[IN]
      ,[OUT] 
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY [User] ORDER BY [Time]) + ROW_NUMBER() OVER (PARTITION BY [User] ORDER BY [Time]) % 2 AS [PairID]
          ,*
    FROM [dbo].[TimeLog]
) DS
PIVOT
(
    MAX([Time]) FOR [Event] IN ([IN], [OUT])
) PVT
ORDER BY [PairID];

Note, I have changed one of your IN event to OUT in order to sanitize the data. If this is not correct in your real example, I guess you can still use the logic above. For example, with your sample data, the maximum IN event is get and it has no OUT event:

You can play with the code, change the MAX to MIN or extend it with your business logic as well.

Changing Rows to Column in SQL

2 Answers2