0

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?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
nabittened
  • 23
  • 1
  • 6

1 Answers1

2

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;
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • You can dynamically construct the pivot statement and then use prepare and execute to run it. The columns are still hardcoded, but you don't have to do it by hand or know them ahead of time. – Chris Strickland May 01 '22 at 15:43
  • Yes, perhaps it's more accurately stated, the values must be fixed in the query at the time it is parsed. How they get there is up to you, but the point is you can't discover the values in the same query that you do the pivot. – Bill Karwin May 01 '22 at 16:17
  • yes, mySQL needs some kind of pivot function. I see the question come up a lot. – Chris Strickland May 01 '22 at 16:42
  • I know of the `PIVOT` syntax in Oracle and Microsoft SQL Server, and the `CROSSTAB()` function in PostgreSQL. Both of those also require that a fixed set of fields are explicit in the query at the time it is parsed. Do you know of another implementation that allows a single query to generate a variable number of columns for itself, as it reads values in the data? – Bill Karwin May 01 '22 at 17:09
  • I don't. So I suppose I should have said that SQL in general needs a dynamic pivot function. I presume it's difficult to implement or SQL would already have it. Looking over your book excerpts (from Antipatterns) right now. Very interesting. – Chris Strickland May 01 '22 at 17:25
  • 1
    I think a dynamic pivot function would conflict with the concept in a relational database that the result of a query must have a fixed set of columns, and the columns have names. This makes the result of a query itself a _relation_, and therefore it can be an operand of another relational operation. For example, if you tried to union the result of a pivot with another relation, it must have the same columns in number and type. This could not be validated unless the columns were known at the time of parsing, not sometime later during execution. – Bill Karwin May 01 '22 at 17:45