CODEWITHSUNDEEP
mysqlpivot-table
Ivan
Ivan

Reputation: 15932

Problems to PIVOT using GROUP_CONCAT

Following this article, I've been able to partly transpose a mySQL table. This is the table:

CREATE TABLE  `test`.`treat` (
`patient` INT NOT NULL ,
`pathology` VARCHAR( 15 ) NOT NULL ,
`present` VARCHAR( 15 ) NOT NULL
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_unicode_ci;

then, insert some data:

INSERT INTO `test`.`treat` (`patient`, `pathology`, `present`) 
VALUES ('1', 'myeloid', 'yes'), ('2', 'lymphoid', 'yes'), ('2', 'myeloid', 'no'), ('3', 'myeloid', 'no'), ('4', 'lymphoid', 'yes');

and now I need to get what patients are affected by lymphoid or myeloid pathologies:

SELECT patient,
  GROUP_CONCAT(if(pathology='myeloid', present, NULL)) AS 'myeloid',
  GROUP_CONCAT(if(pathology='lymphoid', present, NULL)) AS 'lymphoid'
FROM treat
GROUP BY patient

I get a table like this that shows when a patient has suffered one of these pathologies or NULL if there is not information available:

patient  myeloid  lymphoid
1        yes      NULL
2        no       yes
3        no       NULL
4        NULL     yes

These data correspond to periodic tests. I've ommited the test parameter for simplicity (test INT NOT NULL). But the problem is when I have two data for the same patient. For example, let's add the test key and a new test for patient 2 (all previous test values corresponds to test 1):

INSERT INTO `test`.`treat` (`patient`, `pathology`, `present`, `test`) 
VALUES ('2', 'myeloid', 'yes', '2');

If we run the query we get this table:

patient  myeloid  lymphoid
1        yes      NULL
2        no,yes   yes
3        no       NULL
4        NULL     yes

Patient number 2 has a myeloid line that is produced by *GROUP_CONCAT* with two comma separated values. I need to have a line for each value, like this:

patient  myeloid  lymphoid
1        yes      NULL
2        no       yes
2        yes      NULL
3        no       NULL
4        NULL     yes

What have to do to take into account test number?

Upvotes: 0

Views: 778

Answers (1)

user1191247
user1191247

Reputation: 12973

Just add test to your grouping -

SELECT
    patient,
    GROUP_CONCAT(IF(pathology='myeloid', present, NULL)) AS 'myeloid',
    GROUP_CONCAT(IF(pathology='lymphoid', present, NULL)) AS 'lymphoid'
FROM treat
GROUP BY patient, test

Upvotes: 1

Related Questions