With a table like:
Name Event Time
Steve Start 19:53
Steve Event1 19:51
Steve Stop 19:45
Steve Start 18:13
Steve Event2 18:01
Steve Event1 17:51
Steve Stop 17:45
Bob Start 19:47
Bob Event2 19:31
Bob Stop 17:57
representing events that occur between start and stops times. I'd like to turn this into:
Name Start Stop Event1 Event2
Steve 19:53 19:45 1 0
Steve 18:13 17:45 1 1
Bob 19:47 17:57 0 1
My attempt:
select
name,
(CASE event WHEN 'start' THEN time ELSE NULL END) AS Start,
(CASE event WHEN 'stop' THEN time ELSE NULL END) AS Stop,
(CASE event WHEN 'event1' THEN 1 ELSE 0 END) AS Event1,
(CASE event WHEN 'event2' THEN 1 ELSE 0 END) AS Event2
from atable
which results in:
Name Start Stop Event1 Event2
Steve 19:53 0 0 0
Steve 0 0 1 0
Steve 0 19:45 0 0
Steve 18:13 0 0 0
Steve 0 0 0 1
Steve 0 0 1 0
Steve 0 17:45 0 0
Bob 19:47 0 0 0
Bob 0 0 1 0
Bob 0 17:57 0 0
How do I get the records in a single row?
I did try a Group_CONCAT per:
select
name,
GROUP_CONCAT((CASE event WHEN 'start' THEN time ELSE 0 END)) AS Start,
GROUP_CONCAT((CASE event WHEN 'stop' THEN time ELSE 0 END)) AS Stop,
GROUP_CONCAT((CASE event WHEN 'event1' THEN 1 ELSE 0 END)) AS Event1,
GROUP_CONCAT((CASE event WHEN 'event2' THEN 1 ELSE 0 END)) AS Event2
from atable
group by name, event
But ended up with:
Name Start Stop Event1 Event2
Bob 0 0 1 0
Bob 19:47 0 0 0
Bob 0 17:57 0 0
Steve 0,0 0,0 1,1 0,0
Steve 0 0 0 1
Steve 19:53,18:13 0,0 0,0 0,0
Steve 0,0 19:45,17:45 0,0 0,0
And now tested: Working SQL FIDDLE
How do I get the records in a single row?
Max or min would combine the rows, but since name isn't unique enough we need a 2nd way to identify the group within the group which would result in a 2nd row in your output. We can do this using user variables on the event start and a counter.
This assumes:
event for the same user denote a new row needed in result..
***DDL***
CREATE table atable (
name varchar(10),
`event` varchar(10),
`time` time);
INSERT into atable values
('Steve', 'Start', '19:53'),
('Steve', 'Event1', '19:51'),
('Steve', 'Stop', '19:45'),
('Steve', 'Start', '18:13'),
('Steve', 'Event2', '18:01'),
('Steve', 'Event1', '17:51'),
('Steve', 'Stop', '17:45'),
('Bob', 'Start', '19:47'),
('Bob', 'Event2', '19:31'),
('Bob', 'Stop', '17:57');
***DML***
SELECT
name,
max(CASE event WHEN 'start' THEN time ELSE NULL END) AS Start,
max(CASE event WHEN 'stop' THEN time ELSE NULL END) AS Stop,
max(CASE event WHEN 'event1' THEN 1 ELSE 0 END) AS Event1,
max(CASE event WHEN 'event2' THEN 1 ELSE 0 END) AS Event2
FROM (SELECT A.*, CASE WHEN @Name=Name and Event = 'Start' THEN @GRP:=@GRP+1
WHEN @Name <> Name THEN @GRP:=@GRP+1
ELSE @GRP
end as mGROUP,
CASE WHEN @Name <> NAME then
@Name:=Name
END as Name2
FROM atable A
CROSS JOIN (Select @GRP:=0) x
CROSS JOIN (Select @Name:='') y
ORDER BY Name, Time Desc) z
GROUP BY Name, mGROUP
How this works: The inner query "Z" adds two columns, mGROUP and Name2 to your base data set "atable". The Database engine initializes variables @GRP and @NAME via a cross join. We need @GRP so we can group each sub set of data together from atable to make it easier to combine the data. This column represents the missing group by we need in order to use min/max to combine the rows in your pivot.
The @Name variable is used as a control break method so we know when to increment the @grp counter. This value is reset in the Name2 column when the names vary. There are two times the @grp counter needs to be incremented. Once is when a 'Start' event is encountered, once is when @Name changes. The mGROUP column does this increment for us and when no increment is needed, the same value is output as it's part of the same group. (To see this work just run the inner query "Z" and evaluate the results)
Now that we have the needed grouping mechanism to combine the groups using max, we can use simple aggregation to combine the rows which is done via the outer query.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments