Improve company productivity with a Business Account.Sign Up

x
?
Solved

MySQL query output columns from rows

Posted on 2014-03-03
5
Medium Priority
?
675 Views
Last Modified: 2014-03-04
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!
0
Comment
Question by:Ron1959
5 Comments
 
LVL 17

Expert Comment

by:Kent Dyer
ID: 39902575
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
 
LVL 62

Accepted Solution

by:
Julian Hansen earned 2000 total points
ID: 39902707
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
 

Author Closing Comment

by:Ron1959
ID: 39903175
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
 
LVL 62

Expert Comment

by:Julian Hansen
ID: 39903184
You are welcome - thanks for the points.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39903201
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

587 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question