Filtering for multiple parameters within the same column in mySQL

Question

I have a table that lists the various parameters and respective values for each part id. It is formatted as such:

Part ID	Parameter	Value
0001	length	1
0001	width	2
0001	height	3
0002	length	5.3
0002	width	6
0002	height	0.2

However, the actual table has a lot more parameters. How can I query this table in such a way to get results in the following form?

Part ID	length	width
0001	1	2
0002	5.3	6

I also only want to display some of these parameters, and hide others. I have used multiple JOINS to join the table to itself multiple times, but this feels clunky and I would have to do this at least 10 times. Is there another way to get the data in this form?

Does this answer your question? [MySQL pivot table query with dynamic columns](https://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns) — ahmed, Jul 25 '22 at 10:47

DRapp · Answer 1 · 2022-07-25T14:20:54.047

you can do with simple query using case/when to the specific "parameter" which is not really a parameter in context though.

select
      yt.partId,
      max( case when yt.Parameter = 'length'
         then yt.value else null end ) Length,
      max( case when yt.Parameter = 'width'
         then yt.value else null end ) Width
   from
      YourTable yt
   group by
      yt.partId

Just continue with any additional Parameter values you need

FEEDBACK.

Per you inquiry to do multiple at the same time... Yes, you can, just re-join to the same table, but only have one of them as the "primary" based on one parameter that you know will always exist such as the length. Then join on the same ID PLUS the other parameter value

select
      yt.partId,
      yt.value Length,
      ytWidth.value Width,
      ytHeight.value Height,
      yt.value + ytWidth.value LengthPlusWidth
   from
      YourTable yt

         JOIN YourTable ytWidth
           on yt.partId = ytWidth.partId
          AND ytWidth.Parameter = 'width'

         JOIN YourTable ytHeight
           on yt.partId = ytHeight.partId
          AND ytHeight.Parameter = 'height'
   where
      yt.Parameter = 'length'

Here, notice the additional aliases ( ytWidth, ytHeight) are specific to the parameter in question. And now, you can continue with all following. But if there is an instance where a SPECIFIC parameter may NOT exist, make sure to do as a LEFT-JOIN, otherwise those records will be left out of the result.

Is there a way to make a new column that adds length and width together without copy-pasting the whole max() function? — astrojoe, Jul 25 '22 at 11:23

Filtering for multiple parameters within the same column in mySQL

1 Answers1