I'm relatively new to SQL, but I have a query,
select top 3 LoginDateTime from UserLogins where Username = 'John Doe' order by LoginDateTime desc
You have to have something to pivot on to identify which column should get what value, so you could create a row_number and then pivot that data. here is an example with test data.
DECLARE @UserLogins AS TABLE (LoginDateTime DATETIME, UserName VARCHAR(20)) INSERT INTO @UserLogins (LoginDateTime, UserName) VALUES (GETDATE(),'John Doe') ,(GETDATE() -1,'John Doe') ,(GETDATE() -2,'John Doe') ,(GETDATE() -3,'John Doe') ,(GETDATE() -4,'John Doe') ;WITH cteRowNum AS ( SELECT TOP (3) UserName ,LoginDateTime ,RowNum = ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY LoginDateTime DESC) FROM @UserLogins WHERE UserName = 'John Doe' ORDER BY LoginDateTime DESC ) SELECT UserName , as [1st] , as [2nd] , as [3rd] FROM cteRowNum PIVOT ( MAX(LoginDateTime) FOR RowNum IN (,,) ) p
Note the SELECT TOP(3) and order by isn't necessary I just kept it there as a limiter. If there are less than 3 logins the column(s) for that count will be null, more than 3 just don't get displayed.....