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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Julian HansenCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.