Wrong results returned by a table pivot

[Biggoby]

I'm working with MySQL 5.0.25 form phpMyAdmin 2.6.3 and I get strange results when trying to pivot a table...
My table "Test" is very simple and looks like :

MatchID    UserID    Presence
1    1    1
1    2    0
1    3    1
1    4    1
2    1    1
2    2    0
2    3    1
2    4    1
 

I want to display it like:
MatchID User1 User2 User3 User4
1
2

My SQL query is very simple:

SELECT MatchID, 
    CASE UserID WHEN 1 THEN Presence ELSE 6 END AS User1,
    CASE UserID WHEN 2  THEN Presence  ELSE 6 END AS User2,
    CASE UserID WHEN 3 THEN Presence ELSE 6 END AS User3,
    CASE UserID WHEN 4 THEN Presence ELSE 6 END AS User4
FROM Test2
GROUP BY MatchID
 

Very simple...
but I get a very strange result...

MatchID    User1    User2    User3    User4
1    6    6    1    6
2    6    6    1    6
 

I'm lost... Any help would be appreciated...

[ronverdonk]

It is not clear to me what you want to accomplish here. I tested your select, but now showed the UserID, replace the 6 by a blank for readability and removed the GROUP BY. The result is as follows, just what you would expect:

mysql> SELECT MatchID, UserID,
    ->     CASE UserID WHEN 1 THEN Presence ELSE '' END AS User1,
    ->     CASE UserID WHEN 2 THEN Presence ELSE '' END AS User2,
    ->     CASE UserID WHEN 3 THEN Presence ELSE '' END AS User3,
    ->     CASE UserID WHEN 4 THEN Presence ELSE '' END AS User4
    -> FROM Test;
+---------+--------+-------+-------+-------+-------+
| MatchID | UserID | User1 | User2 | User3 | User4 |
+---------+--------+-------+-------+-------+-------+
|       1 |      1 | 1     |       |       |       |
|       1 |      2 |       | 0     |       |       |
|       1 |      3 |       |       | 1     |       |
|       1 |      4 |       |       |       | 1     |
|       2 |      1 | 1     |       |       |       |
|       2 |      2 |       | 0     |       |       |
|       2 |      3 |       |       | 1     |       |
|       2 |      4 |       |       |       | 1     |
+---------+--------+-------+-------+-------+-------+
8 rows in set (0.00 sec)
 

Ronald :cool:

[Biggoby]

Thanks for the reply Ronald,
I may have not been very clear on what I want to achieve:
Here is how I would like my table to be displayed:

+---------+--------+-------+-------+-------+-------+
| MatchID | User1  | User2 | User3 | User4 |
+---------+--------+-------+-------+-------+-------+
|      1  | 1      |   0   | 1     | 1     |
|      2  | 1      |   0   | 1     | 1     |
+---------+--------+-------+-------+-------+-------+
 

where UserI corresponds to the UserID I.
I read everywhere about cross tabs and pivoting table and this is exactly what I want to achieve, but it seems that my code is not working properly.
Any hints?

[ronverdonk]

All I could think of in this particular case, where you have only 1 and 0 values, is to SUM them. So the query would be like:

SELECT MatchID, SUM(user1) as user1, SUM(user2) as user2, 
       SUM(user3) as user3, SUM(user4) as user4 
       FROM (SELECT MatchID, 
CASE UserID WHEN 1 THEN Presence ELSE '0' END AS User1,
CASE UserID WHEN 2 THEN Presence ELSE '0' END AS User2,
CASE UserID WHEN 3 THEN Presence ELSE '0' END AS User3,
CASE UserID WHEN 4 THEN Presence ELSE '0' END AS User4
FROM Test) as result GROUP BY MatchID;

That will, for this case, result in:

+---------+-------+-------+-------+-------+
| MatchID | user1 | user2 | user3 | user4 |
+---------+-------+-------+-------+-------+
|       1 |     1 |     0 |     1 |     1 |
|       2 |     1 |     0 |     1 |     1 |
+---------+-------+-------+-------+-------+

Sorry I cannot help you any further. Maybe there are members who can.
ANYONE???

Ronald :cool:

[Biggoby]

Thanks a lot Ronald!!! It's working now!
Still, I can't figure out why a sum over the field is working while a simple call to the value of the field isn't, but anyway... It works now!

Cheers,
Maïté