Creating a view in MySQL with columns created from row data in a table?

Question

I've got a MySQL database containing three tables. The database contains information about various electrical and mechanical components. It has three tables.

Tables:

componentSource - contains information about where the information in the database was sourced from.
component - contains part number information, description, etc. Multiple entries will refer to a single entry in the componentSource table as its source (Each source file describes multiple components).
componentParams - contains parametric information about the components. Multiple parameter entries will refer to a single entry the component table (each component has multiple parameters).

See simplified example tables...

Database Tables and Relationships:

+-------------------------------+
| Table: componentSource        |
+-------------------------------+
| compSrcID*    | sourceFile    |
+-------------------------------+
| 1             | comp1.txt     |
| 2             | comp2.txt     |
| 3             | comp3.txt     |
+-------------------------------+
    ^
    |
    +---------------------------------------------------+
                  ( many to one reference)              |
                                                        ^
                                                        ^
+---------------------------------------------------------------+
| Table: component                                              |
+---------------------------------------------------------------+
| compID*   | partNum   | mfrPartNum    | mfr       | compSrcID |
+---------------------------------------------------------------+
| 1         | 1234      | ABCD          | BrandA    | 1         |
| 2         | 2345      | BCDE          | BrandB    | 1         |
| 3         | 3456      | CDEF          | BrandC    | 3         |
| 4         | 4567      | DEFG          | BrandD    | 2         |
+---------------------------------------------------------------+
    ^
    |
    +---------------+ (many to one reference)
                    |
                    ^
                    ^
+-------------------------------------------------------+
| Table: componentParams                                |
+-------------------------------------------------------+
| compParamID*  | compID    | paramName | paramValue    |
+-------------------------------------------------------+
| 1             | 1         | ParamA    | 50            |
| 2             | 1         | ParamB    | 123           |
| 3             | 1         | ParamC    | 10%           |
| 4             | 1         | ParamD    | 0.5           |
| 5             | 1         | ParamE    | Active        |
| 6             | 2         | ParamA    | 25            |
| 7             | 2         | ParamB    | 10K           |
| 8             | 2         | ParamC    | 5%            |
| 9             | 2         | ParamD    | 0.25          |
| 10            | 2         | ParamE    | Proto         |
| 11            | 3         | ParamA    | 53.6          |
| 12            | 3         | ParamE    | Active        |
| 13            | 4         | ParamY    | 123-56        |
| 14            | 4         | ParamZ    | True          |
+-------------------------------------------------------+

I would like to create a view of the database that merges information from the three tables. I would like to have a row for each line in the component table that merges the relevant lines from the componentSource table, and all of the relevant parameters out of the componentParams table.

See example view...

Database View:

+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| View: componentView                                                                                                                                   |
+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| compID*   | partNum   | mfrPartNum    | mfr       | SourceFile    | ParamA    | ParamB    | ParamC    | ParamD    | ParamE    | ParamY    | ParamZ    |
| 1         | 1234      | ABCD          | BrandA    | comp1.txt     | 50        | 123       | 10%       | 0.5       | Active    |           |           |
| 2         | 2345      | BCDE          | BrandB    | comp1.txt     | 25        | 10K       | 5%        | 0.25      | Proto     |           |           |
| 3         | 3456      | CDEF          | BrandC    | comp3.txt     | 53.6      |           |           |           | Active    |           |           |
| 4         | 4567      | DEFG          | BrandD    | comp2.txt     |           |           |           |           |           | 123-56    | True      |
+-------------------------------------------------------------------------------------------------------------------------------------------------------+

Since I want a line in the view for each component in the component table, I think merging the info from the componentSource table is fairly straight forward with a join, but the tricky part is creating columns in the view that correspond to the value in componentParam.paramName column. Seems like this requires some recursion to read all parameters associated with a component. Also note that not all components have all the same parameters in the parameter table, so the values for the parameters not used by a component would be null.

An alternative to creating a view, if that can't be done, would be to build another database table.

My SQL skills are super rusty, and were probably not up to this task when they were fresh.

Is it possible to create a view that creates columns that are based on row data (paramName) in a table? Could you show an example?

If not, can a table be built that does the same? Again, could you show an example?

Many thanks.

If you don't know all (future) parameters when you write the query, this can't be done with a view. A separate table with redundant data also doesn't sound like a good idea. A stored procedure which builds the query dynamically should work. But i'd rather "pivot" the data in application language. — Paul Spiegel, Oct 03 '17 at 21:10
Seriously consider handling issues of data display in application code — Strawberry, Oct 03 '17 at 21:32
MySQL does not have a native 'pivot' or 'crosstab' support and will be your limitation here. You can always fake it... https://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns Postgres, MSSQL, Oracle, and pretty much any other DBMS handles this much cleaner — Twelfth, Oct 03 '17 at 22:11
Thank you for the comments. The solution given by Radim works for what I need, which was a quick and dirty solution. I understand that the 'best' solution would be to do the display formatting in the application. The solution below, for example, requires statically defining the parameter columns (foreknowledge of all the parameter names). Programmatically, this could be dynamic. Thanks again for the comments. — Dave L., Oct 05 '17 at 12:27

Radim Bača · Accepted Answer · 2017-10-03T21:10:00.373

Conditional aggregation can do the pivoting for you

SELECT cp.compID,
       ct.partNum, 
       ct.mfrPartNum,
       ct.mfr,
       cs.SourceFile,
       MAX(CASE WHEN cp.paramName = 'ParamA' THEN cp.ParamValue END) as ParamA,
       MAX(CASE WHEN cp.paramName = 'ParamB' THEN cp.ParamValue END) as ParamB,
       MAX(CASE WHEN cp.paramName = 'ParamC' THEN cp.ParamValue END) as ParamC,
       MAX(CASE WHEN cp.paramName = 'ParamD' THEN cp.ParamValue END) as ParamD,
       MAX(CASE WHEN cp.paramName = 'ParamE' THEN cp.ParamValue END) as ParamE,
       MAX(CASE WHEN cp.paramName = 'ParamY' THEN cp.ParamValue END) as ParamY,
       MAX(CASE WHEN cp.paramName = 'ParamZ' THEN cp.ParamValue END) as ParamZ

FROM componentParameters cp
JOIN component ct ON cp.compId = ct.compId
JOIN componentSource cs ON cs.compSrcID = ct.compSrcID
GROUP BY cp.compID,
       ct.partNum, 
       ct.mfrPartNum,
       ct.mfr,
       cs.SourceFile

It is also possible to use subqueries for this, however, I guess this should do the job better.

Thank you. This does exactly what I need. – Dave L. Oct 05 '17 at 12:11 — Dave L., Oct 05 '17 at 12:11

Creating a view in MySQL with columns created from row data in a table?

1 Answers1