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...
4  7198 
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:
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?
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:
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é
Sign in to post your reply or Sign up for a free account.
Similar topics
by: pb648174 |
last post by:
I have a pivot table implementation, part of which is posted below. It
returns no errors in query analyzer, but when profiler is run, it shows
that "Error 208" is happening. I looked that up in BOL...
|
by: Rob |
last post by:
I'm just getting around to using pivot tables and charts. I find the
Pivot table interface to be INCREDIBLY frustrating.
When I view a table in Design view, then choose Pivot table view, I
get...
|
by: Grey |
last post by:
I have created a asp.net form for user to input data. After input the data,
user need to click a button to export the input data to excel for data
analysis with excel pivot table function. is it...
|
by: Peter |
last post by:
Hello all,
I have the following t-sql batch:
create procedure stp_test
(
@p_date1 as datetime = null,
@p_date2 as datetime = null
)
|
by: Spartaco |
last post by:
This is a test I made that I believe has a wrong solution, they say the
correct answer is C ?!?
----
You are an asp.net developer and currently you are working on te sql
statement you will use...
|
by: PeteCresswell |
last post by:
I've got something called "Reference Rates".
The idea is that on a given day, we have various rates of return for
various entities.
e.g. Libor 3-month return, Libor 6-month return, US Treasury...
|
by: Pourya99 |
last post by:
Hello,
I have an Access Data Access Page which has a pivot table. The data source of the pivot table is a SQL database table. The data in the pivot table itself is not a problem.
I have a text...
|
by: ravindarjobs |
last post by:
Hello friends,
I am using ms access 2003, and Vb6.
i am displaying a table in a form as pivot table (following "Auto Form: Pivot Table" Wizard)
Usually i select the items i want to be...
|
by: is49460 |
last post by:
Good afternoon!
I use transfer spreadsheet function the export data from one of the table into the excel spreat sheet. I use the following code:
DoCmd.TransferSpreadsheet acExport, 8, "qry...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |
