MySQL query syntax...
Hi there.
I want to make a query based on a table with 542 unique individuals, and each of these 542 individuals has got up to 6 events each (time series). Right now there is up to 6 rows for each individual. I want to "drag" those events out in one row. Now I made some CASE WHEN obs=1 THEN date END as date1 etc. and grouped by individual and event. I get the desired columns, BUT they're still in separate rows. How do I merge them to only 1 row for each individual?
Best regards
Ulrich
I want to make a query based on a table with 542 unique individuals, and each of these 542 individuals has got up to 6 events each (time series). Right now there is up to 6 rows for each individual. I want to "drag" those events out in one row. Now I made some CASE WHEN obs=1 THEN date END as date1 etc. and grouped by individual and event. I get the desired columns, BUT they're still in separate rows. How do I merge them to only 1 row for each individual?
Best regards
Ulrich
von Axl
Could you provide the whole query with column headers?
ASKER
It's like this (simplified edition with only 2 of the up to 6 events):
SELECT patient_id, CASE WHEN after_serie=1 THEN seriedate END AS seriedate1, CASE WHEN after_serie=1 THEN after_serie END AS after_serie1, CASE WHEN after_serie=1 THEN il_2 END AS il_2_1, CASE WHEN after_serie=1 THEN introna END AS introna1, CASE WHEN after_serie=2 THEN seriedate END AS seriedate2, CASE WHEN after_serie=2 THEN after_serie END AS after_serie2, CASE WHEN after_serie=2 THEN il_2 END AS il_2_2, CASE WHEN after_serie=2 THEN introna END AS introna2
FROM tbl_behandling WHERE SUBSTRING(patient_id, 9, 2)='01' GROUP BY patient_id, after_serie
... and I want only 1 row for each patient_id.
SELECT patient_id, CASE WHEN after_serie=1 THEN seriedate END AS seriedate1, CASE WHEN after_serie=1 THEN after_serie END AS after_serie1, CASE WHEN after_serie=1 THEN il_2 END AS il_2_1, CASE WHEN after_serie=1 THEN introna END AS introna1, CASE WHEN after_serie=2 THEN seriedate END AS seriedate2, CASE WHEN after_serie=2 THEN after_serie END AS after_serie2, CASE WHEN after_serie=2 THEN il_2 END AS il_2_2, CASE WHEN after_serie=2 THEN introna END AS introna2
FROM tbl_behandling WHERE SUBSTRING(patient_id, 9, 2)='01' GROUP BY patient_id, after_serie
... and I want only 1 row for each patient_id.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry... and thank you for the nicer layout. ;-) But it still generate multiple rows for each patient_id.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Problem solved.