Data in a row rather than a column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Data in a row rather than a column

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2009-01-29 : 20:54:22

I have the following, which returns this below but I would prefer the three pieces of data to be in one row rather then one column?:

4 SJH 0 2009-01-27 23:17:12 <This is the users first login time
4 SJH 0 2009-01-29 04:39:09 <This is the users last login time
4 SJH 0 2009-01-29 06:07:39 <This is the users last logout time

Select [UserID], [LoginName], [IsDeleted], [SessionStartTime] AS [Login/out_Time]
FROM (Select DISTINCT [UserID], [LoginName], [IsDeleted], CONVERT(VARCHAR(19), [SessionStartTime], 120) AS [SessionStartTime], CONVERT(VARCHAR(19), [SessionEndTime], 120) AS [SessionEndTime], ROW_NUMBER() Over (Partition by [UserID] Order by [SessionStartTime]) AS FirstLoginTime_PAR
FROM #t
) AS E
WHERE E.FirstLoginTime_PAR = 1
GROUP BY E.UserID, E.LoginName, E.IsDeleted, E.[SessionStartTime]

UNION ALL

Select [UserID], [LoginName], [IsDeleted], MAX([SessionStartTime]) AS [Login/out_Time]
FROM (Select DISTINCT [UserID], [LoginName], [IsDeleted], CONVERT(VARCHAR(19), [SessionStartTime], 120) AS [SessionStartTime], CONVERT(VARCHAR(19), [SessionEndTime], 120) AS [SessionEndTime], ROW_NUMBER() Over (Partition by [UserID] Order by [SessionStartTime] DESC) AS LastLoginTime_PAR
FROM #t
) AS E
WHERE E.LastLoginTime_PAR = 1
GROUP BY E.UserID, E.LoginName, E.IsDeleted, E.[SessionStartTime]

UNION ALL

Select [UserID], [LoginName], [IsDeleted], MAX([SessionEndTime]) AS [Login/out_Time]
FROM (Select DISTINCT [UserID], [LoginName], [IsDeleted], CONVERT(VARCHAR(19), [SessionStartTime], 120) AS [SessionStartTime], CONVERT(VARCHAR(19), [SessionEndTime], 120) AS [SessionEndTime], ROW_NUMBER() Over (Partition by [UserID] Order by [SessionEndTime] DESC) AS LastLogoutTime_PAR
FROM #t
) AS E
WHERE E.LastLogoutTime_PAR = 1
GROUP BY E.UserID, E.LoginName, E.IsDeleted, E.[SessionEndTime]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-29 : 21:08:56

What should be your expected output?

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2009-01-29 : 21:31:46

UserID, IsDeleted, first login time, last login time, last logout time

4 SJH 0 2009-01-27 23:17:12 2009-01-29 04:39:09 2009-01-29 06:07:39

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-29 : 21:38:02

See PIVOT function. Search for dynamic pivot if it needs to be dynamic.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-01-29 : 23:53:01

I think you're mixing your row_numbers, max etc.
For any user, you're looking for the min start, max start and max end. That's it isn't it?

Select [UserID], [LoginName], [IsDeleted],
min([SessionStartTime]) AS [Login/out_Time],
MAX([SessionStartTime]), as lastLogin
MAX([SessionEndTime]) as lastlogout
group by [UserID], [LoginName], [IsDeleted]

unless I've missed something

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2009-01-30 : 02:11:21

Thankyou, that was it. I thought I had tried that? Not sure why I didnt get it. Must of had a moment it has been a busy day.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources