asked on

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!

Kent Dyer

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

ASKER CERTIFIED SOLUTION

Julian Hansen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ron1959

ASKER

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!

Julian Hansen

You are welcome - thanks for the points.

Ray Paseur

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