Re: Convert Specific Row Data into Columns

[David Joyce]

Thanks, I think I'm getting closer. I tried:

SELECT
Name,
( CASE event WHEN 'Start' THEN time ELSE 0 END ) AS 'Start',
( CASE event WHEN 'Stop' THEN time ELSE 0 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 (
SELECT
name, time, event, COUNT(*) AS Data
FROM aTable as stats
GROUP BY name, event) as stats;

and the output is:

Name Start Stop Event1 Event2
Bob 0 0 1 0
Bob 00:19:47 0 0 0
Bob 0 00:17:57 0 0
Steve 0 0 1 0
Steve 0 0 0 1
Steve 00:19:53 0 0 0
Steve 0 00:19:45 0 0

How do I collapse the 3 Bob rows and 4 Steve rows into a single row each?