how to create dynamic pivot tables in MySQL

Question

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.

My problem is as follows:

I have 2 tables in my database. clients contains customer information such as name, ssn and some personal data. In the cli_location table are client locations: address, telephone number, mobile phone and so on.

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.

I often need to cross these two tables. I currently do a left join. The problem with the left join is that sometimes the client has more than one phone, which generates new lines for each one.

When researching how to improve this behavior I discovered pivot tables, however I am not able to understand them.

1st table (clients):

╔════╦══════╦═══════╦════════════╦═══════╦════════╗
║ 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 ║
╚════╩══════╩═══════╩════════════╩═══════╩════════╝

2st table(cli_location):

╔════╦══════╦═══════╦══════════╦════════╦═════════╦═══════╦══════════╗
║ 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 ║
╚════╩══════╩═══════╩══════════╩════════╩═════════╩═══════╩══════════╝

When crossing the tables using left join, with the field of reference being ssn, the result has many duplicate lines. I am not able to change the structure of the data in production.

I had thought about doing something like :

╔════╦══════╦═══════╦════════════╦═══════╦════════╦════════╦════════╦════════╦════════╦══════════╦════════╦═════════╦═══════╦══════════╦════════╦════════╦════════╦══════════╦════════╦════════╦════════╦══════════╦══════════╦══════════╦══════════╗
║ 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)   ║
╚════╩══════╩═══════╩════════════╩═══════╩════════╩════════╩════════╩════════╩════════╩══════════╩════════╩═════════╩═══════╩══════════╩════════╩════════╩════════╩══════════╩════════╩════════╩════════╩══════════╩══════════╩══════════╩══════════╝

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 store, the value of field value differs so columns must be added for each store-value pair. The same for the address and phone fields as in the table above.

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!

Answer 1

Edit

Thinking about this further, I'm confident that SQL can not do exactly what you need. This answer explains why, and lists your choices:

1. write application code to build a query dynamically, or
2. run your existing left join query, and then write application code to manipulate the result into the desired format.

However, if you're comfortable combining multiple values into one column, group_concat might get you close:

select
  ssn,
  name,
  group_concat(distinct tel separator ', ') phones,
  group_concat(distinct address separator '; ') addresses
from
  (select clients.ssn,
    clients.name,
    cli_location.tel,
    concat(cli_location.address, ', ', city, ' ', zip) address
  from clients 
  left join cli_location
    on clients.ssn=cli_location.ssn) c
group by ssn

produces:

|   SSN | NAME |                                 PHONES |                                                        ADDRESSES |
|-------|------|----------------------------------------|------------------------------------------------------------------|
| 12345 | john | 23432122, 44323455, 65765567, 98765434 | street1, city1 23443; street3, city4 76543; street2, city5 54323 |
| 54321 | alex |                               86643457 |                                           street34, city30 43234 |

http://sqlfiddle.com/#!2/ca287/25

Original

If you want a list of customers in a particular city:

select clients.ssn, clients.name
from clients
inner join cli_location
  using(ssn)
where city = 'city1'
group by ssn;

http://sqlfiddle.com/#!2/ca287/3

Your schema is making this more difficult than it should be though. Consider normalizing it so that client names are in only one table, and split one-to-many relationships (like client:phone) into separate tables.