MySQL query output columns from rows

I want to query a table called Vote_Record and output row data in columns, as follows:

Table: Vote_Record
+-------+---------+--------+
| Name	| Issue| Vote	|
+-------+---------+--------+
| Tom	| A	|  Yes	|
| Dick	| A	|  No	|
| Harry	| A	|  No	|
| Tom	| B	|  Yes	|
| Dick	| B	|  Yes	|
| Harry	| B	|  No	|
| Tom	| C	|  Yes	|
| Dick	| C	|  Yes	|
| Harry	| C	|  Yes	|
+-------+---------+--------+

Query Output
+---------+--------------+--------------+-------------+
| Name	| Issue A	| Issue B      | Issue C   |
+---------+--------------+--------------+-------------+
| Tom      | Yes        | Yes            | Yes           |
| Harry	| No         | No             | Yes          |
+---------+--------------+--------------+-------------+

Open in new window


Sorry for the crazy table formatting -- I couldn't figure out how line up the columns...

Thanks!
Ron1959Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Julian HansenConnect With a Mentor Commented:
Assuming you have only 3 issue types then you can use the following query - if you have more then extend to match
SELECT `name`, 
	MAX(CASE WHEN issue='A' THEN vote END) IssueA, 
	MAX(CASE WHEN issue='B' THEN vote END) IssueB, 
	MAX(CASE WHEN issue='C' THEN vote END) IssueC
FROM `table`
GROUP BY `name`;

Open in new window

0
 
Kent DyerIT Security Analyst SeniorCommented:
Baiscally, you are looking to do a PIVOT Query..

something like this..

select t3.name, t3.Issue_A, t3.Issue_B, t3.Issue_C  
from
(select t2.name as name, 
case when Issue_1 then "Yes"
else "No" end  Issue_A, 

case when Issue_2 then "Yes"
else "No" end Issue_B,

case when Issue_3 then 'Yes"
else "No" end Issue_C

from 
(SELECT partners.name as name, sales.products_id as prodid, count(products.name) as counts
FROM test.sales left outer join test.partners on sales.partners_id = partners.id
left outer join test.products on sales.products_id = products.id 
where sales.partners_id = partners.id and sales.products_id = products.id group by partners.name, prodid) t2) t3

group by t3.name ;

Open in new window

0
 
Ron1959Author Commented:
Wow, that's a very clean solution.  It should be easy to manage a large number of issues.

Please watch for my next question, which will have to do with "weighting" the votes and summing them for a total score.

Thanks!
0
 
Julian HansenCommented:
You are welcome - thanks for the points.
0
 
Ray PaseurCommented:
Matrix inversion is a standard computer science problem, and there are many ways to do it.  This demonstrates the PHP logic.  

The essential moving parts look something like this (note the position of rows and columns in these two examples).

// CREATE THE EMPTY MATRIX OF COLUMNS (NAMES) AND ROWS (QUESTIONS)
$mat = array();
foreach ($cols as $c)
{
    foreach ($rows as $r)
    {
        $mat[$c][$r] = '-';
    }
}

Open in new window

// CREATE THE EMPTY MATRIX OF COLUMNS (QUESTIONS) AND ROWS (NAMES)
$mat = array();
foreach ($rows as $r)
{
    foreach ($cols as $c)
    {
        $mat[$r][$c] = '-';
    }
}

Open in new window

0
All Courses

From novice to tech pro — start learning today.