Pivotting DateTime Fields into Columns
-
I have the following data:
What I need is to group by the SessionId and have the DateTimes be columns.
I tried to use CASE but it doesn't like the ).
select SessionId,
MAX(case when Event ='LogOn' then DateTime) as 'LogOn',
MAX(case when Event ='LogOff' then DateTime) as 'LogOff'
from vrvApplicationLog
group by SessionId
-
Syntax-wise the CASE expressions are both missing the keyword END. Also, it's not good to enclose column labels in single quotes because although SSMS and VS have no issues with it some other clients might. The proper label delimiter in SQL Server is brackets
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
I agree with Steve.
Also, wouldn't you want the MIN() time for the LogOn?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
It was the missing END. It works perfectly now. There is only one LogOn record per SessionId so a MIN or MAX will have the same results.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply