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

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

I have three tables Patients table: which contains the name of my patients, Controls table: 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 by Nicholaus Lawson for How to convert rows to columns and query on them in Mysql

A way to solve the problem is with subqueries

select patientId from controlValues   where controlId=1 AND value=23   and patientId in (      select patientId       from controlValues       where controlId=2 and value=true  )  

Answer by Strawberry for How to convert rows to columns and query on them in Mysql

Consider the following (I'll ignore the other tables for now):

CREATE TABLE pcv  (patient_id INT NOT NULL  ,control_id INT NOT NULL  ,value    VARCHAR(12) NOT NULL  ,PRIMARY KEY(patient_id,control_id)  );    INSERT INTO pcv VALUES  (1,1,'23'),  (1,2,'true'),   (2,1,'26'),  (2,2,'false');    SELECT * FROM pcv;  +------------+------------+-------+  | patient_id | control_id | value |  +------------+------------+-------+  |          1 |          1 | 23    |  |          1 |          2 | true  |  |          2 |          1 | 26    |  |          2 |          2 | false |  +------------+------------+-------+  

There are two standard solutions. The first is slower but simpler to write:

Solution 1:

SELECT patient_id       , MAX(CASE WHEN control_id = 1 THEN value END) age       , MAX(CASE WHEN control_id = 2 THEN value END) allergy    FROM pcv   GROUP      BY patient_id;  +------------+------+---------+  | patient_id | age  | allergy |  +------------+------+---------+  |          1 | 23   | true    |  |          2 | 26   | false   |  +------------+------+---------+  

Solution 2:

SELECT pcv1.patient_id       , pcv1.value age       , pcv2.value

allergy FROM pcv pcv1 LEFT JOIN pcv pcv2 ON pcv2.patient_id = pcv1.patient_id AND pcv2.control_id = 2 WHERE pcv1.control_id = 1; +------------+-----+---------+ | patient_id | age | allergy | +------------+-----+---------+ | 1 | 23 | true | | 2 | 26 | false | +------------+-----+---------+

Note that when adopting an EAV model, it's still good practice to utilise proper data types where possible. So you might have a table that stores date information, and another that stores string information.

Answer by Nick Pfitzner for How to convert rows to columns and query on them in Mysql

Another way to do it is to count the rows that match if you need to be sure they meet more than your set number of criteria - after all, you may have more than one match! If you want to get sophisticated, set a variable that has the minimum number of records they need to match and go from there.

SELECT  P.ID, P.Name, COUNT(*)  FROM    Patients    P  JOIN    ControlsValues  V ON V.patientId = P.ID  WHERE   (V.value = 23 AND V.ControlId = 1)  OR  (V.value = 'true' AND V.ControlId = 2)  GROUP BY P.ID, P.Name  HAVING COUNT(*) > 1  

Answer by Vladimir Kovpak for How to convert rows to columns and query on them in Mysql

You can represent your rows as columns by join.

select      *  from controlsValues cv1  join controlsValues cv2      on cv1.patientId = cv2.patientId      and cv1.id <> cv2.id -- remove duplicates  where      cv1.contrlId = 1 AND cv1.value = 23      and cv2.contrlId = 2 AND cv2.value = 'true'  ;  

Now you can see your patients who 23 years old and has alergy.

Answer by sneha user3189021 for How to convert rows to columns and query on them in Mysql

try this one

      select * from controlvalues;        +----+----------+-------+-----------+      | id | contrlId | value | patientId |      +----+----------+-------+-----------+      |  1 |        1 | 23    |         1 |      |  2 |        2 | true  |         1 |      |  3 |        1 | 26    |         2 |      |  4 |        2 | false |         2 |      |  5 |        1 | 23    |         3 |      |  6 |        2 | true  |         3 |      +----+----------+-------+-----------+      6 rows in set (0.00 sec)  

mysql> SELECT cv1.patientId p1,cv1.contrlId ctrl1, cv1.value val1,cv2.patientId p2,cv2.contrlId ctrl2, cv2.value val2 FROM controlvalues cv1 , controlvalues cv2 WHERE cv1.patientId = cv2.patientId and cv1.contrlId = 2 and cv1.value = 'true' and cv2.contrlId = 1 and cv2.value = '23';

+------+-------+------+------+-------+------+  | p1   | ctrl1 | val1 | p2   | ctrl2 | val2 |  +------+-------+------+------+-------+------+  |    1 |     2 | true |    1 |     1 | 23   |  |    3 |     2 | true |    3 |     1 | 23   |  +------+-------+------+------+-------+------+  2 rows in set (0.00 sec)  

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72