MYSQL: Join rows as columns

Question

I'm using MySQL, and trying to sort/join a table with multiple rows of data, so that each year of data is represented in adjacent columns, and if data isn't present, it's represented as null.

+------+------+-----------+ | code | year | enquiries | +------+------+-----------+ | 1 | 2012 | 302 | | 2 | 2012 | 274 | | 3 | 2012 | 288 | | 4 | 2012 | 301 | | 5 | 2012 | 192 | | 1 | 2013 | 406 | | 3 | 2013 | 297 | | 4 | 2013 | 199 | | 1 | 2014 | 254 | | 2 | 2014 | 396 | | 3 | 2014 | 187 | | 4 | 2014 | 213 | | 5 | 2014 | 316 | | 6 | 2014 | 222 | +------+------+-----------+

so that the table above would look like the following:

+------+------+-----------+------+-----------+------+-----------+ | code | year | enquiries | year | enquiries | year | enquiries | +------+------+-----------+------+-----------+------+-----------+ | 1 | 2012 | 302 | 2013 | 406 | 2014 | 254 | | 2 | 2012 | 274 | NULL | NULL | NULL | NULL | | 3 | 2012 | 288 | 2013 | 297 | 2014 | 187 | | 4 | 2012 | 301 | 2013 | 199 | 2014 | 213 | | 5 | 2012 | 192 | NULL | NULL | 2014 | 316 | | 6 | NULL | NULL | NULL | NULL | 2014 | 222 | +------+------+-----------+------+-----------+------+-----------+

I think that what I should be looking for is a full outer join, but that isn't possible using MySQL.

I managed to get it to join multiple rows together using multiple LEFT OUTER JOINS, but this omits any rows where the data isn't present for all 3 years.

before table: sqlfiddle.com/#!9/c9be0/1, after table:sqlfiddle.com/#!9/df1e2/1 — n8udd
– n8udd, Commented May 21, 2015 at 13:53

Ravinder Reddy · Accepted Answer · 2015-05-21 13:49:09Z

For the limited number of records you have shown, you can write a simple pivot solution as below.

But, if the number of years increase, you have to generate this query run time and execute.

mysql> select
    ->        code
    ->      , max( case when year=2012 then year end ) as y2012
    ->      , max( case when year=2012 then enquiries end ) as enq_2012
    ->      , max( case when year=2013 then year end ) as y2013
    ->      , max( case when year=2013 then enquiries end ) as enq_2013
    ->      , max( case when year=2014 then year end ) as y2014
    ->      , max( case when year=2014 then enquiries end ) as enq_2014
    ->   from so_20150521.so_q30374911
    ->  group by code
    ->  order by code;
+------+-------+----------+-------+----------+-------+----------+
| code | y2012 | enq_2012 | y2013 | enq_2013 | y2014 | enq_2014 |
+------+-------+----------+-------+----------+-------+----------+
|    1 |  2012 |      302 |  2013 |      406 |  2014 |      254 |
|    2 |  2012 |      274 |  NULL |     NULL |  2014 |      396 |
|    3 |  2012 |      288 |  2013 |      297 |  2014 |      187 |
|    4 |  2012 |      301 |  2013 |      199 |  2014 |      213 |
|    5 |  2012 |      192 |  NULL |     NULL |  2014 |      316 |
|    6 |  NULL |     NULL |  NULL |     NULL |  2014 |      222 |
+------+-------+----------+-------+----------+-------+----------+
6 rows in set (0.02 sec)

Demo on SQL Fiddle at http://sqlfiddle.com/#!9/4a46d/2

Further simplification can be:

mysql> select
    ->        code
    ->      , max( case when year=2012 then enquiries end ) as enq_2012
    ->      , max( case when year=2013 then enquiries end ) as enq_2013
    ->      , max( case when year=2014 then enquiries end ) as enq_2014
    ->   from so_20150521.so_q30374911
    ->  group by code
    ->  order by code;
+------+----------+----------+----------+
| code | enq_2012 | enq_2013 | enq_2014 |
+------+----------+----------+----------+
|    1 |      302 |      406 |      254 |
|    2 |      274 |     NULL |      396 |
|    3 |      288 |      297 |      187 |
|    4 |      301 |      199 |      213 |
|    5 |      192 |     NULL |      316 |
|    6 |     NULL |     NULL |      222 |
+------+----------+----------+----------+
6 rows in set (0.00 sec)

Vickrant · Accepted Answer · 2015-05-21 13:23:01Z

0

Try this:

(SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id) UNION (SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.id)

answered May 21, 2015 at 13:23

Vickrant

1,3312 gold badges9 silver badges15 bronze badges

Collectives™ on Stack Overflow

MYSQL: Join rows as columns

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related