Question 20384 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL display multiple data for each user

I have table:

t_patients (id is auto_increment)

id patient_id created_at
1 100 2023-01-10
2 200 2023-01-11
3 100 2023-01-15

the result need like this:

patient_id 1st come 2nd come 3rd come
100 2023-01-10 2023-01-15 x
200 2023-01-11 x x

I have googling for this but still didn’t find the exact output as needed. What query the SQL should be?

Tags:

2

Answers


  1. 0

    You can achieve this using COALESCE function in mysql and conditional aggregation.

    The MySQL COALESCE() function is used for returning the first non-null
    value in a list of expressions. If all the values in the list evaluate
    to NULL, then the COALESCE() function returns NULL.

    Here is a demo using DBFIDDLE.

    The following query shall give you the expected output :

    SELECT 
  patient_id,
  COALESCE(MAX(IF(rn = 1, created_at, NULL)), 'x') AS `1st come`,
  COALESCE(MAX(IF(rn = 2, created_at, NULL)), 'x') AS `2nd come`,
  COALESCE(MAX(IF(rn = 3, created_at, NULL)), 'x') AS `3rd come`
FROM (
  SELECT 
    id,
    patient_id,
    created_at,
    ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY created_at) AS rn
  FROM t_patients
) AS t
GROUP BY patient_id;

    Output :

    patient_id 1st come 2nd come 3rd come
    100 2023-01-10 2023-01-15 x
    200 2023-01-11 x x
    Login or Signup to reply.
  2. 0

    I wrote a query without using window function (as MySQL 5.7 and below do not support it). To make things more exciting, let’s add a few rows:

    create table t_patients (id int primary key auto_increment,patient_id int,create_at date);
insert t_patients values(default,100,'2023-01-10'),
(default,200,'2023-01-11'),
(default,100,'2023-01-15'),
(default,300,'2023-01-15'),
(default,200,'2023-01-16'),
(default,100,'2023-01-16'),
(default,300,'2023-01-17'),
(default,300,'2023-01-18');

select * from t_patients;
+----+------------+------------+
| id | patient_id | create_at  |
+----+------------+------------+
|  1 |        100 | 2023-01-10 |
|  2 |        200 | 2023-01-11 |
|  3 |        100 | 2023-01-15 |
|  4 |        300 | 2023-01-15 |
|  5 |        200 | 2023-01-16 |
|  6 |        100 | 2023-01-16 |
|  7 |        300 | 2023-01-17 |
|  8 |        300 | 2023-01-18 |
+----+------------+------------+

    After that, let’s write the query which lines up each patient_id based on each patient’s create_at value. A row_id is given for each of a patient’s create_at value , which starts from 1. Note, the row_id value shall be reset to 1 whenever patient_id changes, so every patient_id can have its row_id start from 1.

    select patient_id,create_at , 
case @pid when patient_id then  @row_id:=@row_id+1 else  @row_id:=1 end as row_id ,
case when @pid!=patient_id then @pid:=patient_id else @pid end as track_pid -- the track_pid serves to keep the @pid value up to date 
from
t_patients,
(select @row_id:=0,@pid:=(select max(patient_id) from t_patients) ) t
order by patient_id , create_at
;
+------------+------------+--------+-----------+
| patient_id | create_at  | row_id | track_pid |
+------------+------------+--------+-----------+
|        100 | 2023-01-10 |      1 |       100 |
|        100 | 2023-01-15 |      2 |       100 |
|        100 | 2023-01-16 |      3 |       100 |
|        200 | 2023-01-11 |      1 |       200 |
|        200 | 2023-01-16 |      2 |       200 |
|        300 | 2023-01-15 |      1 |       300 |
|        300 | 2023-01-17 |      2 |       300 |
|        300 | 2023-01-18 |      3 |       300 |
+------------+------------+--------+-----------+

    The above query is then used as a derived table (aliased as tb ) for our main query, which does the table pivoting job. Note: Instead of using the coalesce function, here I decided to use min() function . As numbers serving as strings come before letters in ascii, so min() will pick up the create_at over letter x when a create_at value exists .

    select patient_id,
min(case when row_id=1 then create_at else 'x' end) 1st,
min(case when row_id=2 then create_at else 'x' end) 2nd,
min(case when row_id=3 then create_at else 'x' end) 3rd,
min(case when row_id=4 then create_at else 'x' end) 4th
from
    (select patient_id,create_at , 
    case @pid when patient_id then  @row_id:=@row_id+1 else  @row_id:=1 end as row_id ,
    case when @pid!=patient_id then @pid:=patient_id else @pid end as track_pid
    from
    t_patients,(select @row_id:=0,@pid:=(select max(patient_id) from t_patients) ) t
    order by patient_id , create_at) tb
group by patient_id
order by patient_id
;

+------------+------------+------------+------------+------+
| patient_id | 1st        | 2nd        | 3rd        | 4th  |
+------------+------------+------------+------------+------+
|        100 | 2023-01-10 | 2023-01-15 | 2023-01-16 | x    |
|        200 | 2023-01-11 | 2023-01-16 | x          | x    |
|        300 | 2023-01-15 | 2023-01-17 | 2023-01-18 | x    |
+------------+------------+------------+------------+------+
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search