3

Possible Duplicate:
MySQL pivot table

Should I be using a LEFT JOIN using derived tables, a RIGHT JOIN using derived tables, a different table design, or do I need to query the table and write Python code to produce the desired output shown below?

The output I would like to get to would yield the following output example:

==========================
| 2005   |  2006 |  2007 |  <--- These headings are not necessary
==========================
|   A    |  A    |  A    |
--------------------------
|  AA    |  AA   |  AA   |
--------------------------
|  BB    |  BB   |       |
--------------------------
|  C     |       |   C   |
--------------------------

The data for the query is in a two column table containing (symbol, year):

====================
|  Symbol |  Year  |
====================
|   A     |  2005  |
--------------------
|   AA    |  2005  |
--------------------
|   BB    |  2005  |
--------------------
|   C     |  2005  |
--------------------
|   A     |  2006  |
--------------------
|   AA    |  2006  |
--------------------
|   BB    |  2006  |
--------------------
|   A     |  2007  |
--------------------
|   AA    |  2007  |
--------------------
|   C     |  2007  |
--------------------
Community
  • 1
  • 1
Dr.EMG
  • 159
  • 10
  • Look at [this post](http://stackoverflow.com/questions/7674786/mysql-pivot-table), the question is similar to yours. – Chait Dec 24 '12 at 04:14
  • I would like to humbly suggest that 'how' a question is asked and the 'answer' is given rings differently for every person. Question asked in different ways is a good thing. It might connect with a wider group of readers. After the closing of this question, I located [this post](http://stackoverflow.com/questions/1241178/mysql-rows-to-columns) that is asking the same thing I asked, but in a totally different way. Please, do not be so quick to shoot down and shut off thought. Everyone does not process information in the same manner as others. – Dr.EMG Dec 24 '12 at 16:01
  • Does the closing of a question imply that the question is not useful?. Even with [this post](http://stackoverflow.com/questions/10039273/pivot-table-in-mysql), I see that the SQL logic is the same given for my question. But again, I could not visualize what the code was doing to the data. I believe this is the difference between how I asked the question and how some of the other questions have been asked. – Dr.EMG Dec 24 '12 at 16:19

1 Answers1

5
SELECT MAX(CASE WHEN year = 2005 THEN Symbol ELSE NULL END) `2005`,
       MAX(CASE WHEN year = 2006 THEN Symbol ELSE NULL END) `2006`,
       MAX(CASE WHEN year = 2007 THEN Symbol ELSE NULL END) `2007`
FROM tableName
GROUP BY Symbol

If you have unknown values of year, a Dynamic SQL Query is very much preferred on this.

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN year = ',
      year,
      ' THEN Symbol ELSE NULL end) AS ',
      CONCAT('`', year, '`')
    )
  ) INTO @sql
FROM TableName;

SET @sql = CONCAT('SELECT  ', @sql, ' 
                   FROM tableName 
                   GROUP BY Symbol');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

if you want to show empty string instead of null value, COALESCE is required.

John Woo
  • 258,903
  • 69
  • 498
  • 492