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:

enter image description here

To: enter image description here

• Gerard H. Pille: Although you do want to convert rows to columns, your example is not a pivot.
• Bob: Sorry, I don't explain this very well here. It's more about how to unstack a table.
• Scott Hodgin - Retired: @Bob - What RDBMS are you using (SQL Server, Oracle, etc.) and what version (2008, 2012, 2016)? Also, it would be extremely helpful if you would update your question to include actual table definitions as well as sample data in the form of insert statements so we don't have to type that while testing a solution.

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 |