Using CASE WHEN DATE Statement in MariaDB 10.2.15

I created a query to display the ScanIn Field in one month on each date

Code :

SELECT
    Employee.Name,
    CASE WHEN DAY(LogsFormatted.DateIn) = 1 THEN LogsFormatted.ScanIn ELSE '-' END AS '1',
    CASE WHEN DAY(LogsFormatted.DateIn) = 2 THEN LogsFormatted.ScanIn ELSE '-' END AS '2',
    CASE WHEN DAY(LogsFormatted.DateIn) = 3 THEN LogsFormatted.ScanIn ELSE '-' END AS '3',
    CASE WHEN DAY(LogsFormatted.DateIn) = 4 THEN LogsFormatted.ScanIn ELSE '-' END AS '4',
    CASE WHEN DAY(LogsFormatted.DateIn) = 5 THEN LogsFormatted.ScanIn ELSE '-' END AS '5',
    CASE WHEN DAY(LogsFormatted.DateIn) = 6 THEN LogsFormatted.ScanIn ELSE '-' END AS '6',
    CASE WHEN DAY(LogsFormatted.DateIn) = 7 THEN LogsFormatted.ScanIn ELSE '-' END AS '7',
    CASE WHEN DAY(LogsFormatted.DateIn) = 8 THEN LogsFormatted.ScanIn ELSE '-' END AS '8',
    CASE WHEN DAY(LogsFormatted.DateIn) = 9 THEN LogsFormatted.ScanIn ELSE '-' END AS '9',
    CASE WHEN DAY(LogsFormatted.DateIn) = 10 THEN LogsFormatted.ScanIn ELSE '-' END AS '10'
FROM
    HrEmployee AS Employee
    LEFT JOIN HrAttLogsFormatted AS LogsFormatted ON Employee.FingerId = LogsFormatted.FingerId
WHERE
    LogsFormatted.DateIn BETWEEN '2019-03-01' AND '2019-03-31'
    AND Employee.Id = 14522228

In Database

| DateIn     | ScanIn   |
| 2019-03-04 | 06:58:09 |
| 2019-03-05 | 07:34:09 |
| 2019-03-06 | 07:12:05 |
| 2019-03-08 | 06:56:51 |

Result From Database Click Here

I want the results of the query to display

Name    1   2   3   4         5        6         7  8         9  10
Aldan   -   -   -   06:58:09  07:34:09 07:12:05  -  06:56:51  -  -

But with the query I made unsuccessfully, is there a way to solve it?

Thank you in advance

Answer

I think you want conditional aggregation:

SELECT e.Name,
       MAX(CASE WHEN DAY(lf.DateIn) = 1 THEN lf.ScanIn END) AS '1',
       MAX(CASE WHEN DAY(lf.DateIn) = 2 THEN lf.ScanIn END) AS '2',
       MAX(CASE WHEN DAY(lf.DateIn) = 3 THEN lf.ScanIn END) AS '3',
       MAX(CASE WHEN DAY(lf.DateIn) = 4 THEN lf.ScanIn END) AS '4',
       MAX(CASE WHEN DAY(lf.DateIn) = 5 THEN lf.ScanIn END) AS '5',
       MAX(CASE WHEN DAY(lf.DateIn) = 6 THEN lf.ScanIn END) AS '6',
       MAX(CASE WHEN DAY(lf.DateIn) = 7 THEN lf.ScanIn END) AS '7',
       MAX(CASE WHEN DAY(lf.DateIn) = 8 THEN lf.ScanIn END) AS '8',
       MAX(CASE WHEN DAY(lf.DateIn) = 9 THEN lf.ScanIn END) AS '9',
       MAX(CASE WHEN DAY(lf.DateIn) = 10 THEN lf.ScanIn END AS '10'
FROM HrEmployee e LEFT JOIN
     HrAttLogsFormatted lf
     ON e.FingerId = lf.FingerId
WHERE lf.DateIn BETWEEN '2019-03-01' AND '2019-03-31' AND
      e.Id = 14522228
GROUP BY e.name;

I don’t recommend using a dash for missing data. Just use NULL — what SQL uses.