title: how to create dynamic pivot tables in MySQL
body: <p>I'm having trouble generating pivot tables in MySQL and wonder if anyone can give me any tips and some documentation so I can finally resolve this problem.</p>
  
  <p>My problem is as follows:</p>
  
  <p>I have 2 tables in my database.  <code>clients</code> contains customer information such as name, ssn and some personal data.  In the <code>cli_location</code> table are client locations: address, telephone number, mobile phone and so on.</p>
  
  <p>Sometimes we need to do selects based on criteria such as location, to pick up customers in a particular city.  We also make queries based on cpf to get information for a specific customer.</p>
  
  <p>I often need to cross these two tables.  I currently do a <code>left join</code>. The problem with the <code>left join</code> is that sometimes the client has more than one phone, which generates new lines for each one.</p>
  
  <p>When researching how to improve this behavior I discovered pivot tables, however I am not able to understand them.  </p>
  
  <h1>1st table (clients):</h1>
  
  <pre><code>╔════╦══════╦═══════╦════════════╦═══════╦════════╗
  ║ id ║ name ║  ssn  ║  dt_birth  ║ store ║ value  ║
  ╠════╬══════╬═══════╬════════════╬═══════╬════════╣
  ║  1 ║ john ║ 12345 ║ 1991-11-04 ║   318 ║ 34.33  ║
  ║  2 ║ john ║ 12345 ║ 1991-11-04 ║   318 ║ 654.44 ║
  ║  3 ║ john ║ 12345 ║ 1991-11-04 ║   212 ║ 238.00 ║
  ║  4 ║ alex ║ 54321 ║ 1988-05-20 ║   321 ║ 334.44 ║
  ╚════╩══════╩═══════╩════════════╩═══════╩════════╝
  </code></pre>
  
  <h1>2st table(cli_location):</h1>
  
  <pre><code>╔════╦══════╦═══════╦══════════╦════════╦═════════╦═══════╦══════════╗
  ║ id ║ name ║  ssn  ║ address  ║  city  ║  state  ║  zip  ║   tel    ║
  ╠════╬══════╬═══════╬══════════╬════════╬═════════╬═══════╬══════════╣
  ║  1 ║ john ║ 12345 ║ street1  ║ city1  ║ state1  ║ 23443 ║ 23432122 ║
  ║  2 ║ john ║ 12345 ║ street1  ║ city1  ║ state1  ║ 23443 ║ 98765434 ║
  ║  3 ║ john ║ 12345 ║ street2  ║ city5  ║ state7  ║ 54323 ║ 65765567 ║
  ║  4 ║ john ║ 12345 ║ street3  ║ city4  ║ state9  ║ 76543 ║ 44323455 ║
  ║  5 ║ alex ║ 54321 ║ street34 ║ city30 ║ state33 ║ 43234 ║ 86643457 ║
  ╚════╩══════╩═══════╩══════════╩════════╩═════════╩═══════╩══════════╝
  </code></pre>
  
  <p>When crossing the tables using left join, with the field of reference being <code>ssn</code>, the result has many duplicate lines.  I am not able to change the structure of the data in production.</p>
  
  <p>I had thought about doing something like :</p>
  
  <pre><code>╔════╦══════╦═══════╦════════════╦═══════╦════════╦════════╦════════╦════════╦════════╦══════════╦════════╦═════════╦═══════╦══════════╦════════╦════════╦════════╦══════════╦════════╦════════╦════════╦══════════╦══════════╦══════════╦══════════╗
  ║ id ║ name ║  ssn  ║  dt_birth  ║ store ║ value  ║ store1 ║ value1 ║ store2 ║ value2 ║ address  ║  city  ║  state  ║  zip  ║ address1 ║ city1  ║ state1 ║  zip1  ║ address2 ║ city2  ║ state2 ║  zip2  ║   tel    ║   tel1   ║   tel2   ║   tel3   ║
  ╠════╬══════╬═══════╬════════════╬═══════╬════════╬════════╬════════╬════════╬════════╬══════════╬════════╬═════════╬═══════╬══════════╬════════╬════════╬════════╬══════════╬════════╬════════╬════════╬══════════╬══════════╬══════════╬══════════╣
  ║  1 ║ john ║ 12345 ║ 1991-11-04 ║   318 ║ 34.33  ║ 318    ║ 654.44 ║ 212    ║ 238.00 ║ street1  ║ city1  ║ state1  ║ 23443 ║ street2  ║ city5  ║ state7 ║ 54323  ║ street3  ║ city4  ║ state9 ║ 76543  ║ 23432122 ║ 98765434 ║ 65765567 ║ 44323455 ║
  ║  4 ║ alex ║ 54321 ║ 1988-05-20 ║   321 ║ 334.33 ║ (null) ║ (null) ║ (null) ║ (null) ║ street34 ║ city30 ║ state33 ║ 43234 ║ (null)   ║ (null) ║ (null) ║ (null) ║ (null)   ║ (null) ║ (null) ║ (null) ║ (null)   ║ 86643457 ║ (null)   ║ (null)   ║
  ╚════╩══════╩═══════╩════════════╩═══════╩════════╩════════╩════════╩════════╩════════╩══════════╩════════╩═════════╩═══════╩══════════╩════════╩════════╩════════╩══════════╩════════╩════════╩════════╩══════════╩══════════╩══════════╩══════════╝
  </code></pre>
  
  <p>The idea is to generate columns dynamically as there are more lines with the same type of information, as in the above table.  Even if there are 2 rows with the same <code>store</code>, the value of field <code>value</code> differs so columns must be added for each <code>store</code>-<code>value</code> pair.  The same for the <code>address</code> and <code>phone</code> fields as in the table above.</p>
  
  <p>If someone could point me to some documentation, show me any examples or anything that helps me learn how to do this kind of thing I would be very grateful!</p>