Mysql – How to convert rows into columns and query on them in Mysql

eavMySQLpivot

I have three tables Patients, which contains the name of my patients, Controls which represents the UI controls which could be drawn for each illness , and ControlsValues table which contains the values of the controls submitted for each Patient

Lets have some data
Patients Table

|ID  | Name |
|-----------|
| 1  | Ara  |
| 2  | Sada |

Controls table 

|ID  | Text | Type     |
|-----------|----------|
| 1  | age  | textbox  |
| 2  |alergy| checkbox |

Then the controlsValues table which is where I want to query at

|ID  | contrlId | value    | patientId |
|---------------|----------|-----------|
| 1  | 1        | 23       | 1         |
| 2  | 2        | true     | 1         |
| 3  | 1        | 26       | 2         |
| 4  | 2        | false    | 2         |

here my problem occurs when I want to return that patient from ControlsValues table that has the (controlId=1 AND value=23) and (controlId=2 AND value=true)
in this case the condition is on two rows not two columns which it is not possible , so I desided to change the rows into the columns depending on controlId but I dont know how and I have been searching for 2 days and seen a lot of samples but none of them helped me to solve my problem

Best Answer

You can try this kind of "solution":

http://sqlfiddle.com/#!9/d33a95/18

It is not an "pivot-table" solution, but maybe it solves your problem - if I have understood it correctly.

Here are the tables / data used:

CREATE TABLE `patients` (
`ID` INT NOT NULL AUTO_INCREMENT ,
`Name` VARCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( `ID` )
);
CREATE TABLE `controls` (
`ID` INT NOT NULL AUTO_INCREMENT ,
`Text` VARCHAR( 50 ) NOT NULL ,
`Type` VARCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( `ID` )
); 
CREATE TABLE `controlvalues` (
`ID` INT NOT NULL AUTO_INCREMENT ,
`ControlID` INT NOT NULL ,
`Value` VARCHAR( 50 ) NOT NULL ,
`PatientID` INT NOT NULL ,
PRIMARY KEY ( `ID` )
);
INSERT INTO `patients` (`Name`) VALUES ('Ara'), ('Sada'), ('Pada'), ('Lada');
INSERT INTO `controls` (`Text`, `Type`) VALUES ('age', 'textbox'), ('alergy', 'checkbox');
INSERT INTO `controlvalues` (`ControlID`, `Value`, `PatientID`) VALUES 
  (1, '23', 1), 
  (2, 'true', 1),
  (1, '25', 2),
  (2, 'false', 2),
  (1, '23', 3),
  (2, 'false', 3),
  (1, '23', 4), 
  (2, 'true', 4);

And the query:

SELECT PatientID, COUNT(PatientID) AS PatCount, p.Name
FROM controlvalues cv
LEFT JOIN patients p ON p.ID = cv.PatientID
WHERE ((ControlID = 1 AND Value = '23')
   OR (ControlID = 2 AND Value = 'true'))
GROUP BY PatientID
HAVING PatCount = 2
;
Related Question