1

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

  • Although you do want to convert rows to columns, your example is not a pivot. – Gerard H. Pille Nov 25 '18 at 2:40
  • Sorry, I don't explain this very well here. It's more about how to unstack a table. – Bob Nov 25 '18 at 10:56
  • 1
    @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. – Scott Hodgin Nov 25 '18 at 11:34
0

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       |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.