mysql 用行实现动态列,MYSQL，动态行到列

I have a table user that looks something like this (more columns involved)

user_id | user_name |

1 | adam |

2 | berta |

3 | caesar |

a table product that looks something like this

product_id | product_name |

1 | product 1 |

2 | product 2 |

3 | product 3 |

4 | product 4 |

and a table u_p that refers to products a user is allowed to use

u_p_id | u_p_user | u_p_prod |

1 | 1 | 1 |

2 | 1 | 2 |

3 | 2 | 3 |

where u_p_user and u_p_prod are the foreign keys to user_id resp. product_id.

What I want is this:

user_id | user_name | u_p_prod=1 | u_p_prod=2 | u_p_prod=3 | u_p_prod=4 |

1 | adam | 1 | 1 | 0 | 0 |

2 | berta | 0 | 0 | 1 | 0 |

3 | caesar | 0 | 0 | 0 | 0 |

users and products have to be dynamic, off course.

How can this be done in mySQL?

解决方案

This is often called a pivot. There's no way to do a pivot without hard-coding one column per distinct value. This means you need to know the set of distinct values before you write the SQL query.

SELECT u.user_id, u.user_name,

COALESCE(MAX(IF(u_p.prod=1, 1, 0), 0) AS `u_p_prod=1`,

COALESCE(MAX(IF(u_p.prod=2, 1, 0), 0) AS `u_p_prod=2`,

COALESCE(MAX(IF(u_p.prod=3, 1, 0), 0) AS `u_p_prod=3`,

COALESCE(MAX(IF(u_p.prod=4, 1, 0), 0) AS `u_p_prod=4`

FROM user AS u

LEFT OUTER JOIN u_p ON u.user_id = u_p.u_p_user

GROUP BY u.user_id;