title: Can a MySQL query turn rows into columns?
body: <p>I have a number of tables I am trying to combine with joins but as such, the results are returned in a number of rows whereas I would like to have them generated as new columns. </p>
  
  <h1>member_information Table</h1>
  
  <pre><code>MemberID | FirstName | LastName 
  ---------------------------------
     1     |   John    |  Harris
     2     |   Sarah   |  Thompson
     3     |   Zack    |  Lewis
  </code></pre>
  
  <h1>member_dependent_information Table</h1>
  
  <pre><code>MemberID | FirstName | LastName | Type
  ---------------------------------------
     1     |   Amy     | Harris   |  1 
     2     |   Bryan   | Thompson |  1
     2     |   Dewey   | Thompson |  2
     2     |   Tom     | Thompson |  2
     3     |   Harry   | Lewis    |  2
     3     |   Minka   | Lewis    |  1
  </code></pre>
  
  <h1>MySQL Query:</h1>
  
  <pre><code>SELECT
      t1.FirstName,
      t1.LastName,
      t1.MemberID,
      IF(t2.Type = '1',CONCAT(t2.FirstName,' ',t2.LastName),'') AS Spouse_Name,
      IF(t2.Type = '2',CONCAT(t2.FirstName,' ',t2.LastName),'') AS Child_Name,
  
  FROM
      member_dependent_information t2
  INNER JOIN
      member_information t1
  USING
      (MemberID)
  ORDER BY
      t1.LastName ASC,
      t1.MemberID ASC;
  </code></pre>
  
  <h1>Ideal Results</h1>
  
  <pre><code>MemberID | FirstName | LastName  | Spouse_Name    | Child_Name1   | Child_Name2 
  --------------------------------------------------------------------------------
     1     |   John    |  Harris   | Amy Harris     |     NULL       |   NULL    
     2     |   Sarah   |  Thompson | Bryan Thompson | Dewey Thompson | Tom Thompson    
     3     |   Zack    |  Lewis    | Mika Lewis     | Harry Lewis    |   NULL
  </code></pre>
  
  <h1>ACTUAL RESULTS</h1>
  
  <pre><code>MemberID | FirstName | LastName  | Spouse_Name    | Child_Name 
  -------------------------------------------------------------------
     1     |   John    |  Harris   | Amy Harris     |    NULL
     2     |   Sarah   |  Thompson | Bryan Thompson |    NULL  
     2     |   Sarah   |  Thompson |      NULL      | Dewey Thompson  
     2     |   Sarah   |  Thompson |      NULL      | Tom Thompson    
     3     |   Zack    |  Lewis    | Mika Lewis     |    NULL      
     3     |   Zack    |  Lewis    |      NULL      | Harry Lewis    
  </code></pre>
  
  <p>While my query returns the "correct" data in multiple rows, it does not combine the result into one single row as needed.</p>
  
  <p>The suggestion for Pivot Tables / Crosstabs has been mentioned below but every reference I am able to find suggests using mathematic calculations or that the number of fields to be returned is known. I will not know this information as a single member COULD have up to 100 dependents (although more like 4-8)</p>
  
  <h1>UPDATE #1</h1>
  
  <p>I feel I am getting closer to the final solution. I added the function GROUP_CONCAT to my query which returns ALL firstnames in a single column and ALL last names in a single column but still need to break them out into their own individual columns.</p>
  
  <p>New function is:</p>
  
  <pre><code>SELECT
    t1.MemberID,
    t1.FirstName,
    t1.LastName,
    GROUP_CONCAT(t2.FirstName) AS Dep_Firstnames,
    GROUP_CONCAT(t2.LastName) AS Dep_LastNames
  FROM
    member_information t1
    LEFT OUTER JOIN member_dependent_information t2
      ON t1.MemberID = t2.MemberID
  WHERE
    t1.Status = 1
  GROUP BY
    t1.MemberID
  </code></pre>