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

Question

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

Answer 1

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
;

Answer 2

Exists

You can get your answer without needing to pivot your data. The most direct approach (from my perspective) is using EXISTS, which reflects very directly your statement:

SELECT
     p.ID, p.Name
FROM
     patients p
WHERE
    EXISTS 
    (
        SELECT 1
        FROM controlvalues cv1
        WHERE p.ID = cv1.PatientID
              AND cv1.controlId = 1
              AND cv1.value = '23'
    )
    AND
    EXISTS
    (
        SELECT 1
        FROM controlvalues cv2
        WHERE p.ID = cv2.PatientID
              AND cv2.controlId = 2
              AND cv2.value = 'true'
    ) ;

Which will just return:

ID | Name
-: | :---
 1 | Ara

---

Join

As an alternative, you can change the EXISTS to JOINs:

SELECT
     p.ID, p.Name
FROM
     patients p
     JOIN controlvalues cv1 
         ON cv1.PatientID = p.ID AND cv1.controlId = 1 AND cv1.value = '23'
     JOIN controlvalues cv2 
         ON cv2.PatientID = p.ID AND cv2.controlId = 2 AND cv2.value = 'true' ;

which will return the same results. The query is more concise, but, in my opinion a little bit more difficult to read.

The execution plans (as of MariaDB 10.2) are the same, provided the right indexes (or, in my case, the primary key) have been defined.

---

Pivot

If you really want to pivot your data, that's how you would do it:

SELECT
    p.ID, p.Name,
    max(case when controlId = 1 then value end) AS controlId1 /* age */,
    max(case when controlId = 2 then value end) AS controlId2 /* allergy */
FROM
    patients p
    JOIN controlvalues cv ON cv.PatientId = p.ID 
GROUP BY
    p.ID ;

This is the pivoted table:

ID | Name | controlId1 | controlId2
-: | :--- | :--------- | :---------
 1 | Ara  | 23         | true      
 2 | Sada | 25         | false

... and that's how you would use it:

SELECT
    pp.ID, pp.Name
FROM
    (
    SELECT
        p.ID, p.Name,
        max(case when controlId = 1 then value end) AS controlId1,
        max(case when controlId = 2 then value end) AS controlId2
    FROM
        patients p
        JOIN controlvalues cv ON cv.PatientId = p.ID 
    GROUP BY
        p.ID 
    ) AS pp
WHERE
    pp.controlId1 = '23' AND pp.controlId2 = 'true';

ID | Name
-: | :---
 1 | Ara 

The execution plan for the pivoted table, however, might probably be worse (specially in a big table).

You can check everything in dbfiddle here