MySQL pivot with 3 tables

Question

I've searched and viewed various posts about pivoting (dynamically) data from MySQL. I've managed to get it working with one table, but I am hitting a block when I attempt to do this with three tables. Can someone point me in the right direction?

Table 1: Vendors (List of Vendors we buy from)
Table 2: Criteria (The criteria each Vendor sets)
Table 3: Matrix (A link table for the above two)

What I would like to achieve is a matrix/pivot like this:

           Criteria1        Criteria2        Criteria3 
Vendor 1   ValueFromMatrix  ValueFromMatrix  ValueFromMatrix
Vendor 2   ValueFromMatrix  ValueFromMatrix  ValueFromMatrix
Vendor 3   ValueFromMatrix  ValueFromMatrix  ValueFromMatrix

An example of this table in use:

           MinOrderQty        MinOrderValue        ReturnsAllowed 
Intel      1000                5000.00             RMA required
AMD        2000               15000.00             No
nVidia     9000                8000.00             RMA + dropship

The number of Vendors and Criteria is dynamic and can be added/deleted by users, so I can't hard code the transform from rows to columns.

The ValueFromMatrix is a Varchar(200) field and contains text. It's linked two the other two tables by the use of a VendorID and a CriteriaID (int) fields.

I simply have hit a brick wall here, and I'm not having any luck deciphering some of the examples I've seen.

Table code:

CREATE TABLE `vendors` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `vendor_name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;


CREATE TABLE `criteria` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `criteria_desc` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;


CREATE TABLE `matrix` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vendor_id` int(11) DEFAULT NULL,
  `criteria_id` int(11) DEFAULT NULL,
  `criteria_response` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=latin1;

SQL Fiddle of DB schema/data

The criteria_response field is what populates the ValueFromMatrix in the table example above.

If anyone is able to point me in the right direction, I'd be grateful. I've just not seen any examples that discuss three tables to generate this view, and I keep getting stuck.

Thanks.

score 0 · Accepted Answer · edited May 23 '17 at 11:59

0

Try this query which is adapted from one of my earlier answers (which was adapted from this question/answer):

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN criteria_id = ''',
      criteria_id,
      ''' THEN criteria_response END) AS `',
      criteria_desc, '`'
    )
  ) INTO @sql
FROM  matrix m JOIN criteria c ON m.criteria_id = c.id;

SET @sql = CONCAT('SELECT V.vendor_name, ', @sql, ' 
                  FROM matrix m
                  JOIN vendors v ON m.vendor_id = v.id
                  JOIN criteria c on m.criteria_id = c.id
                  GROUP BY v.id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Sample SQL Fiddle

edited May 23 '17 at 11:59

Community

1
1

answered Aug 23 '15 at 23:21

jpw

44,361
6
66
86

1

That's great, works perfectly, thank you! Totally wouldn't have got that on my own. – SparkyUK Aug 23 '15 at 23:46
@SparkyUK try adding a where clause to the first statement: _WHERE criteria_desc != 'ReturnsAllowed';_ – jpw Aug 24 '15 at 00:08
thanks jpw, I figured it out and deleted my comment, but thanks for confirming I've put that in the correct place. :) – SparkyUK Aug 25 '15 at 01:02
One other question regarding the Where Clause - I'm doing Where c.id IN (1,4) which brings back Criteria ID 1 and 4, obviously. Why does this not work when I pass it as a SP parameter? Where c.id in (SelectedCols) ? I've created a SP with your code, added (In SelectedCols Text) at the top of the SP, but I just get the error: "expected 1 parameter, got 2". Any ideas? – SparkyUK Aug 25 '15 at 01:05
@SparkyUK You can't pass arrays or lists as arguments that way. There are ways of doing it though; I'm not familiar with how this is best done in MySQL but see this answer for some possible guidance: http://stackoverflow.com/a/11957706/1175077 – jpw Aug 25 '15 at 01:09
@SparkyUK You could post a new question about how to pass multiple values to a proc for use with an in clause (although I'm sure this must have been asked and answered before so it would probably be flagged as a duplicate, but at least then you would have some idea). – jpw Aug 25 '15 at 01:11
thanks again, jpw. I'd just started looking at find_in_set() but it was returning the number of instances rather than actually selecting the columns to add to the resultset. I'll dig further through that link you gave me as it does indeed look like it might help. Thanks for the speedy replies. – SparkyUK Aug 25 '15 at 01:17

MySQL pivot with 3 tables

1 Answers1

Linked