-1

Essentially I have a table in my database called Table1 with the following data:

enter image description here

The table has a ProductID that repeats because the values of AssignedColour, ColourFinding and ColourPower vary.

I would like to present all ProductID data in one single row, meaning if there is more than one AssignedColour, ColourFinding and ColourPower listed, it will contain a number at the end.

The final result I of the SELECT query should look like the following:

enter image description here

The number of columns presented horizontally is based on the number of AssignedColour per ProductID

Is something like this possible to accomplish in a mySQL SELECT Query?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
John
  • 965
  • 8
  • 16
  • That's called "pivoting". – The Impaler Jul 12 '20 at 16:47
  • Easy to do in MySQL 8.x. Are you using MySQL 5.x or 8.x? – The Impaler Jul 12 '20 at 16:48
  • A query returns pre-defined columns. If you know the maximum number of colors when writing the query, you can return as many columns. If you don't know it, you can't write the query. Generally, this nothing you would normally do in SQL anyway, but rather in your app or Website. – Thorsten Kettner Jul 12 '20 at 16:54
  • Seriously consider handling issues of data display in application code – Strawberry Jul 12 '20 at 17:03
  • The solution of this question is not SQL-related. This should be implemented on the app side. You should remove the SQL & MySQL tags, and add the programming language you are using instead. – The Impaler Jul 12 '20 at 17:06
  • I have done this sort of thing in MS Access but I am interested in how it can be done more efficiently in MySQL. – John Jul 12 '20 at 17:08
  • Here is a stored procedure to do the work: http://mysql.rjweb.org/doc.php/pivot – Rick James Jul 12 '20 at 21:38

1 Answers1

0

An SQL query cannot expand the number of columns of the result set depending on the data values it discovers during query execution. The columns in the SELECT-list must be fixed at the time the query is prepared, before it reads any data.

Also the column names cannot be changed during the query execution. They must be set at the time the query is prepared.

There's no way to do what you are describing in a single SQL query. Your options are:

  • Do two queries: one to enumerate the colors per product, and then use the result of the first to format a second query with the columns you want.
  • Do one query to fetch the data in rows as it exists in your table, then write code in your app to display it in rows however you think is best.

Either way, you have to write at least a bit of code in the client. You can't do this in one query.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Understood. Do you mind showing me an example of how this can be done using the 2 Query method? I would greatly appreciate it – John Jul 12 '20 at 17:34
  • https://stackoverflow.com/questions/8977855/mysql-dynamic-cross-tab/8977988#8977988 – Bill Karwin Jul 12 '20 at 17:42