Pivot information - sql table

Question

I was wondering if anyone could help me with pivoting data in sql? I am a newbies and need some help

I want to pivot data based on "LastLogOn" column. Is it possible to do in SQL?

From:

To:

Answer 1

You did not specify your RDBMS, but here is a solution that uses SQL Server and dynamic SQL. If you are using a different RDBMS, you could probably get it to work with minimal changes.

Since you are generating column headings (Username1, Username2, etc.) based on how many UserName's have the same LastLogon value, I used an intermediate table (PivotTemp) to store both the original data as well as the generated column names.

I added a few more rows to the test data to verify the output.

--Demo setup
set nocount on
DECLARE @cols AS NVARCHAR(MAX),@colsCoalesce AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);
drop table if exists Table1
create table Table1 (LastLogon datetime, UserId int, UserName1 varchar(30))

insert into table1(LastLogon,UserId, UserName1) values
('2018-06-14 21:00:00',1,'John'),
('2018-06-20 15:03:00',2,'Jack'),
('2018-06-14 15:31:00',3,'Mary'),
('2018-06-18 07:18:54',4,'Ben'),
('2018-06-18 07:18:54',5,'Simon'),
('2018-06-18 07:18:54',5,'Barry'),
('2018-06-20 17:03:54',6,'Andrew'),
('2018-06-20 17:03:54',6,'Stuart')

--The solution
drop table if exists PivotTemp
select * into PivotTemp from 
(
select LastLogon, UserName1 as UserName, 'UserName' + convert(varchar(5),ROW_NUMBER() over(partition by LastLogon order by LastLogon)) as GeneratedUserNameHeading from table1
)a
--select * from PivotTemp
SElect @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.GeneratedUserNameHeading) 
            FROM PivotTemp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
SElect @colsCoalesce = STUFF((SELECT distinct ',' + 'coalesce(' + QUOTENAME(c.GeneratedUserNameHeading) + ',''N/A'') as ' + QUOTENAME(c.GeneratedUserNameHeading)
            FROM PivotTemp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

print @cols
print @colsCoalesce
--select * from PivotTemp
set @query = 'SELECT LastLogon, ' + @colsCoalesce + ' from 
            (
                select LastLogon
                    , UserName
                    , GeneratedUserNameHeading
                from PivotTemp
           ) x
            pivot 
            (
                 max(UserName)
                for GeneratedUserNameHeading in (' + @cols + ')
            ) p '


execute(@query)

---

| LastLogon               | UserName1 | UserName2 | UserName3 |
|-------------------------|-----------|-----------|-----------|
| 2018-06-14 15:31:00.000 | Mary      | N/A       | N/A       |
| 2018-06-14 21:00:00.000 | John      | N/A       | N/A       |
| 2018-06-18 07:18:54.000 | Ben       | Simon     | Barry     |
| 2018-06-20 15:03:00.000 | Jack      | N/A       | N/A       |
| 2018-06-20 17:03:54.000 | Andrew    | Stuart    | N/A       |