Group some fields

Hello dear friend.

Another question, which i need your help

table1:

| id | name | works | hours |
----------------------------
| 1 | bob | wrk1 | 1 |
| 2 | bob | wrk2 | 3 |
| 3 | bob | wrk3 | 2 |
| 4 | bob | wrk4 | 3 |
| 5 | tom | wrk1 | 2 |
| 6 | tom | wrk4 | 1 |
| 7 | tom | wrk3 | 3 |
| 8 | ann | wrk2 | 1 |
| 9 | ann | wrk3 | 3 |
|... | ... | ... | ... |

Here `wrkx` used one time in each name, e.g. `bob` can `wrk1` only one time or none time.

Can I group by name and get such kind of results:

| id | name | wrk1 | wrk2 | wrk3 | wrk4 | ... | wrkN | - came from table1.works
----------------------------------------------------
|1 | bob | 1 | 3 | 2 | 3 | null| null |
|2 | tom | 2 | null | 3 | 1 | null| null |
|3 | ann | null | 1 | 3 | null | null| null |

There is `wrkN` - N < 30

Pardon me my english.

Thanks.

 

What you're suggesting is equivalent to a vertical lookup or horizontal aggregation. You need to do it on the application level. It's not really practical to do it within MySQL unless you use some really complicated querying, if it is at all possible.

 

It easy, but tedious. Essentially you create an aggragate query that sums a bunch of conditional fields like this:

SELECT name, SUM(IF(works='wrk1', hours, 0)) AS wrk1, SUM(IF(works='wrk2', hours, 0)) AS wrk2....
FROM table1 GROUP BY name;

PHP:

Just keep adding: SUM(IF(works='wrkn', hours, 0)) AS wrkn
to account for every wrkn possible.

 

I think so, thank You.

 

Thank You,
another question, in this case e.g. tom has no wrk2 , so result must be null (not integer). How can i get such result

 

Just replace 0 with NULL.

 

Thank you.