djmordigal djmordigal - 1 month ago 6
SQL Question

How to get rid of null values in pivoted table?

I am trying to pivot a table in MySQL so that I can turn row values into column headers for an easier-to-read table in a report. Here is a sample of what comes out so far (data has been obfuscated a bit but the concept is still the same):

+------------+------+------+---------+
| Date | RS1 | RS2 | RS3 |
+------------+------+------+---------+
| 2007-02-26 | 12 | NULL | NULL |
| 2007-04-12 | 8 | NULL | NULL |
| 2007-07-31 | 9 | NULL | NULL |
| 2008-02-07 | 12 | NULL | NULL |
| 2008-11-06 | 20 | NULL | NULL |
| 2010-03-04 | 8 | NULL | NULL |
| 2010-12-16 | 7 | NULL | NULL |
| 2011-01-24 | 20 | NULL | NULL |
| 2011-03-22 | 7 | NULL | NULL |
| 2012-11-12 | 19 | NULL | NULL |
| 2007-02-26 | NULL | 18 | NULL |
| 2007-04-12 | NULL | 2 | NULL |
| 2007-07-31 | NULL | 4 | NULL |
| 2008-02-07 | NULL | 10 | NULL |
| 2008-11-06 | NULL | 8 | NULL |
| 2010-03-04 | NULL | 6 | NULL |
| 2010-12-16 | NULL | 6 | NULL |
| 2011-01-24 | NULL | 5 | NULL |
| 2011-03-22 | NULL | 11 | NULL |
| 2012-11-12 | NULL | 13 | NULL |
| 2007-02-26 | NULL | NULL | 15 |
| 2007-04-12 | NULL | NULL | 6 |
| 2007-07-31 | NULL | NULL | 5 |
| 2008-02-07 | NULL | NULL | 8 |
| 2008-11-06 | NULL | NULL | 11 |
| 2010-03-04 | NULL | NULL | 1 |
| 2010-12-16 | NULL | NULL | 19 |
| 2011-01-24 | NULL | NULL | 14 |
| 2011-03-22 | NULL | NULL | 15 |
| 2012-11-12 | NULL | NULL | 10 |
+------------+------+------+---------+
30 rows in set (0.00 sec)


The only thing left to do is to get rid of the
NULL
values so that all the columns are aligned with each other for each date. In other words, delete the last 20 rows from column 1, the first 10 and last 10 from column 2, and the first 20 from column 3, so that all the columns and their values are aligned.

Is there an easy way to do this in MySQL?

Answer

Try this:

SELECT Date,
       MAX(RS1),
       MAX(RS2),
       MAX(RS3)
FROM Table1
GROUP BY Date

SQLFiddle demo