[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MySQL query output columns from rows

Posted on 2014-03-03
5
Medium Priority
?
670 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 60

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 60

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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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…
Suggested Courses
Course of the Month19 days, 5 hours left to enroll

834 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