Mysql – Add data from rows to different columns if more than 1 row have a partial match

MySQLpivot

I tried to find a solution but do not find anything to add multiple columns from partially matching rows.

Data:(Maximum of 3 programs per person)

name     surname    program
Jan      Peters     program1
Jan      Peters     program2
Pete     Peters     program1
Jane     Smith      program3
Jane     Smith      program1
Jane     Smith      program2

Result needed:

name     surname    prog1       prog2      prog3
Jan      Peters     program1    program2
Pete     Peters     program1
Jane     Smith      program3    program1   program2

Even just pointing me into a direction would help.

Best Answer

Welcome.

Try this.

select name, surname, 
    max(prog1) as prog1, max(prog2) as prog2, max(prog3) as prog3 
 from
    (select name, surname, 
     if(program= 'program1', 'program1', '') as prog1, 
     if(program= 'program2', 'program2', '') as prog2, 
     if(program= 'program3', 'program3', '') as prog3 
    from db1) as tempdb
 group by name, surname;

This is possible only if the three program values are fixed / known prior to the code (itself).

For unknown values of program this is not possible directly. In such case, you may have to move them to another table / temporary table / view by multiplying columns and then assembling the same into one with more columns.

Related Solutions

Mysql – One to many to many

You can simplify that specific query:

SELECT COUNT(1)
FROM results r 
JOIN steps s 
    ON s.result_id=r.id
WHERE r.monitor_id = 64 
  AND r.completed >= '2014-10-01' 
  AND r.completed <= '2014-10-03'

If you don't have it already, try indexes like:

CREATE INDEX ... ON results (monitor_id, completed, id);
CREATE INDEX ... ON steps (result_id, id);

MySQL – Adding Columns and Index to a Huge Table

First of all, use one alter statement to do all of adding two columns and the compound index. ALTER TABLE table1 ADD col1 INT, ADD col2 CHAR(2), ADD INDEX (col1, col5);. More about ALTER
Archiving old records is a good idea. If it is very rarely needed, then some slowness is affordable (I think).
Depending on your engine that you use, dumping the data and importing it to a new table maybe slow if you can't disable the keys. If you drop all indexes and recreate them, that is slow again. If you can afford some down time, any method is ok.
If the tables keeps growing, and you don't want to archive, you may consider "Sharding" the table, but that would require some extra efforts at development level
Last idea, which I don't strongly recommend unless you can't afford downtime, is to create a new table with a primary key and the new column that you want to add. Use this PK to join the original table.

Best Answer

Related Solutions

Mysql – One to many to many

MySQL – Adding Columns and Index to a Huge Table

Related Question