Multiple Row -> One Row

[mattbru]

I need to combine multiple rows into one row for a query.  Given the table:

 

TIME  |  ID  |  STATUS

9          1        GOOD

9          2        BAD

9          3        GOOD

10        1        GOOD

10        2        GOOD

10        3        BAD

 

I would want:

 

TIME  |  ID 1  |  ID 2  |  ID 3 

9          GOOD    BAD    GOOD

10        GOOD    GOOD  BAD

 

Also, there may be a variable number of ID's per time (not just 3).

 

Any ideas?

[kickstart]

Hi

 

Can't see a way to do that. It would bring back a variable number of columns.

 

You could use GROUP_CONCAT to put the fields into one:-

 

SELECT `TIME` GROUP_CONCAT(`STATUS` SEPARATOR ',')
FROM SomeTable
GROUP BY `TIME`

 

You could then explode the status field in php if you wanted.

 

All the best

 

Keith

[mattbru]

Hi

 

Can't see a way to do that. It would bring back a variable number of columns.

 

You could use GROUP_CONCAT to put the fields into one:-

 

SELECT `TIME` GROUP_CONCAT(`STATUS` SEPARATOR ',')
FROM SomeTable
GROUP BY `TIME`

 

You could then explode the status field in php if you wanted.

 

All the best

 

Keith

 

 

Okay, thanks.  Suppose it was limited to a specific number, say 5 ID's per time?

[kickstart]

Hi

 

Possibly, but cannot see a way of doing it without it being either too complicated for something simple or too slow (or both).

 

Something like this might work:-

 

SELECT DISTINT `TIME`, b.STATUS, c.STATUS, d.STATUS, e.STATUS, f.STATUS
FROM SomeTable a
LEFT OUTER JOIN (SELECT STATUS FROM SomeTable z WHERE z.`TIME` = a.`Time LIMIT 0,1) b ON a.`TIME` = b.`TIME`
LEFT OUTER JOIN (SELECT STATUS FROM SomeTable y WHERE y.`TIME` = a.`Time LIMIT 1,1) c ON a.`TIME` = c.`TIME`
LEFT OUTER JOIN (SELECT STATUS FROM SomeTable x WHERE x.`TIME` = a.`Time LIMIT 2,1) d ON a.`TIME` = d.`TIME`
LEFT OUTER JOIN (SELECT STATUS FROM SomeTable w WHERE w.`TIME` = a.`Time LIMIT 3,1) e ON a.`TIME` = e.`TIME`
LEFT OUTER JOIN (SELECT STATUS FROM SomeTable v WHERE v.`TIME` = a.`Time LIMIT 4,1) f ON a.`TIME` = f.`TIME`

 

but not a nice idea (and not tested at all).

 

All the best

 

Keith

[fenway]

That's something called a "cross-tab".

[aschk]

Try this out:

SELECT `time`
     ,IF(SUM(IF(`id`=1, `status`,0))=1,'GOOD','BAD') AS 'id1'
     ,IF(SUM(IF(`id`=2, `status`,0))=1,'GOOD','BAD') AS 'id2'
     ,IF(SUM(IF(`id`=3, `status`,0))=1,'GOOD','BAD') AS 'id3'
FROM <insert table name here>
GROUP BY `time`;