title: MySQL: Using IF statement to pivot, but getting lots of NULL values
body: <p>I have some data organized by quintile labels (-1, 1, 2, 3, 4, 5). For each of these values in a <code>Quintile</code> column, there is a value in another column called <code>ret</code>. Lastly, there is a column of dates containing month-end dates as integers.</p>
  
  <p>My goal is to visualize all of the Quintile returns data at the same time, each as its own column, with only the date column acting like an index.</p>
  
  <p>Essentially, I want to pivot on the <code>Quintile</code> column and I have seen other places advising the use of <code>IF</code> statements in MySQL as a way to achieve this.</p>
  
  <p>For example, here is a query that would show one Quintile's worth of the data:</p>
  
  <pre><code>select yearmonth, ret
  where Quintile=1
  from quintile_returns
  </code></pre>
  
  <p>But I don't want to repeat this for all Quintile labels, save out the data individually, and piece it together in Python Pandas or Excel or something. I want to make SQL show it as distinct columns.</p>
  
  <p>But when I try this <code>IF</code> statement style poor man's pivot, this is the query I use:</p>
  
  <pre><code>select yearmonth, 
         IF(Quintile=1, ret, NULL) as Q1_ret,
         IF(Quintile=2, ret, NULL) as Q2_ret
  from quintile_returns
  </code></pre>
  
  <p>I basically get a diagonal of valid data back. All the rows where the Quintile is not 1 still show up, populated with NULL, and then so on for Quintile 2.</p>
  
  <p>How do I avoid all of these extra NULL values? Basically, I want to tell SQL to return the column's value <em>only</em> if the condition is satisfied, and do not use NULL or anything else as a default <code>else</code>-like placeholder. </p>
  
  <p>Is there a way to do this that does not involve nested join-type statements?</p>