He Who Shall Not Be Named He Who Shall Not Be Named - 1 year ago 64
SQL Question

SQL - pivot three rows into three columns with static column names?

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

That returns the three most recent logins.

How would I go about pivoting the query so that I end up with a result where each date is in its own column, and where the column headers are '1st', '2nd', and '3rd'?


ADDENDUM: The query is running on MS SQL Server targetting specifically the content of the result (which may change in future to include more than just the top three logins). It could even become a case where the column titles themselves have to change over time, so static titles (as opposed to programmatically generated) would be the ideal solution.

Answer Source

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)
    (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)
           ,RowNum = ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY LoginDateTime DESC)
           UserName = 'John Doe'
        ORDER BY
           LoginDateTime DESC

        ,[1] as [1st]
        ,[2] as [2nd]
        ,[3] as [3rd]
        PIVOT (
           FOR RowNum IN ([1],[2],[3])
        ) 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.....

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download