How to print table like(see image) that in mysql?

Question

My table format

mysql> desc attendance;
+--------+------------+------+-----+---------+----------------+
| Field  | Type       | Null | Key | Default | Extra          |
+--------+------------+------+-----+---------+----------------+
| aid    | bigint(20) | NO   | PRI | NULL    | auto_increment |
| sid    | int(10)    | YES  | MUL | NULL    |                |
| cid    | bigint(20) | YES  | MUL | NULL    |                |
| ttid   | bigint(20) | YES  | MUL | NULL    |                |
| did    | int(3)     | YES  | MUL | NULL    |                |
| date   | date       | YES  |     | NULL    |                |
| hour   | varchar(3) | YES  |     | NULL    |                |
| stuid  | bigint(20) | YES  | MUL | NULL    |                |
| status | varchar(8) | YES  |     | NULL    |                |
+--------+------------+------+-----+---------+----------------+
9 rows in set (0.47 sec)

mysql>

My table result is

mysql> select * from attendance;
+-----+------+------+------+------+------------+------+-------+---------+
| aid | sid  | cid  | ttid | did  | date       | hour | stuid | status  |
+-----+------+------+------+------+------------+------+-------+---------+
|   1 |    2 |   13 |    4 |    3 | 2020-03-25 | p1   |    16 | present |
|   2 |    2 |   13 |    4 |    3 | 2020-03-25 | p1   |    17 | absent  |
|   3 |    2 |   13 |    4 |    3 | 2020-03-25 | p1   |    18 | present |
|   4 |    2 |   13 |    4 |    3 | 2020-03-25 | p1   |    19 | absent  |
|   5 |    2 |   13 |    4 |    3 | 2020-03-25 | p1   |    20 | present |
|   6 |    2 |   13 |    4 |    3 | 2020-03-25 | p1   |    15 | absent  |
|   7 |    2 |   13 |    2 |    2 | 2020-03-17 | p6   |    16 | present |
|   8 |    2 |   13 |    2 |    2 | 2020-03-17 | p6   |    17 | absent  |
|   9 |    2 |   13 |    2 |    2 | 2020-03-17 | p6   |    18 | present |
|  10 |    2 |   13 |    2 |    2 | 2020-03-17 | p6   |    19 | absent  |
|  11 |    2 |   13 |    2 |    2 | 2020-03-17 | p6   |    20 | present |
|  12 |    2 |   13 |    2 |    2 | 2020-03-17 | p6   |    15 | absent  |
|  13 |    2 |   13 |    4 |    3 | 2020-03-25 | p2   |    16 | present |
|  14 |    2 |   13 |    4 |    3 | 2020-03-25 | p2   |    17 | present |
|  15 |    2 |   13 |    4 |    3 | 2020-03-25 | p2   |    18 | present |
|  16 |    2 |   13 |    4 |    3 | 2020-03-25 | p2   |    19 | present |
|  17 |    2 |   13 |    4 |    3 | 2020-03-25 | p2   |    20 | present |
|  18 |    2 |   13 |    4 |    3 | 2020-03-25 | p2   |    15 | present |
|  19 |    2 |   13 |    4 |    3 | 2020-03-25 | p8   |    16 | absent  |
|  20 |    2 |   13 |    4 |    3 | 2020-03-25 | p8   |    17 | absent  |
|  21 |    2 |   13 |    4 |    3 | 2020-03-25 | p8   |    18 | present |
|  22 |    2 |   13 |    4 |    3 | 2020-03-25 | p8   |    19 | present |
|  23 |    2 |   13 |    4 |    3 | 2020-03-25 | p8   |    20 | present |
|  24 |    2 |   13 |    4 |    3 | 2020-03-25 | p8   |    15 | absent  |
+-----+------+------+------+------+------------+------+-------+---------+
24 rows in set (0.00 sec)

I need predefined table result will be like this. Please refer attached image. Only print present or absent under p1 , p2, upto p8. Those result must be student id, cid, ttid,did, date based result. Can anyone do this?

*My table format* Replace with its CREATE TABLE. *result will be like this.* Show result which absolutely matches to sample source data. — Akina, Mar 31 '20 at 04:51
I need attendance table format will be image attached format in select query. How to do that — TECHNO SUBBU, Mar 31 '20 at 04:53
Pivotting in MySQL is problematic task. Either you use stored procedure or perform pivotting on the client side. I'd recommend the last. — Akina, Mar 31 '20 at 04:55
Can you do that? Please do that? I attached this query into JDBC — TECHNO SUBBU, Mar 31 '20 at 04:57
If you want to do this on MySQL side nevertheless see, for example, https://dba.stackexchange.com/a/213820/150107 — Akina, Mar 31 '20 at 04:57

score 0 · Answer 1 · answered Mar 31 '20 at 05:15

0

You want to create a pivot table result from your query. I would recommend use a case statement in your query to get the desire result.

Check pivot table in mysql

Check similar issue on stackoverflow

answered Mar 31 '20 at 05:15

Pankaj Rawat

4,037
6
41
73

There is SUM and COUNT is there. But, I don't need count and sum. I need exact string print under p1. Like absent or present under p1 column header – TECHNO SUBBU Mar 31 '20 at 05:32
So when you will do grouping and you don't have duplicate data result will remain correct. – Pankaj Rawat Mar 31 '20 at 05:37
Will you explain it in clear way? – TECHNO SUBBU Mar 31 '20 at 05:39
If your data in unique with combination (sid | cid | ttid | did | date | hour) and you will put sum on your query you will get same value. – Pankaj Rawat Mar 31 '20 at 05:53
https://blog.webnersolutions.com/mysql-how-to-convert-row-values-into-column-names/ – TECHNO SUBBU Mar 31 '20 at 09:21
I follow above link but returns 0 – TECHNO SUBBU Mar 31 '20 at 09:22

How to print table like(see image) that in mysql?

1 Answers1