how to make pivot mysql table as follows?

Question

I want show data according date wise with geoup by User ID.

The first shows the data stored in database and second table shows how i want to show the data on front end.

This is the table in Database :-

UserID    Date        Working Hrs
1           2021-08-01    10
2           2021-08-01    1
3           2021-08-01    15
1           2021-08-02    11
2           2021-08-02    11
3           2021-08-02    16
1           2021-08-03    9
2           2021-08-03    10
3           2021-08-03    11

This is the table i want to create from db table :-

UserID  2021-08-01  2021-08-02  2021-08-03
1        10            11           9
2        1             11           10
3        15            16           11

If the names of pivot columns are static then use conditional aggregation. If not then use dynamic SQL in stored procedure. — Akina, Sep 15 '21 at 07:25
Does this answer your question? [MySQL pivot row into dynamic number of columns](https://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns) — Ermenegildo, Sep 22 '21 at 11:20

score 0 · Accepted Answer · answered Sep 15 '21 at 08:03

0

If your columns are static then you can use conditional aggregation. Otherwise use pivot or dynamic.

-- MySQL(v5.8)
SELECT userid
     , MAX(CASE WHEN tdate = '2021-08-01' THEN working_hours END) "2021-08-01"
     , MAX(CASE WHEN tdate = '2021-08-02' THEN working_hours END) "2021-08-02"
     , MAX(CASE WHEN tdate = '2021-08-03' THEN working_hours END) "2021-08-03"
FROM test
GROUP BY userid

Please check from url https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=d364ddf6f942a54beddb2ba719d1932f

answered Sep 15 '21 at 08:03

Rahul Biswas

3,207
2
10
20

@SunilNatwadiya please check this answer and let me know any query regard this. – Rahul Biswas Sep 15 '21 at 09:55
the date column are dynamic...? – Sunil Natwadiya Sep 16 '21 at 06:27
@SunilNatwadiya my solution is based on your given result set for few columns. If full month required then try some dynamic which handle 30 days or 31 days a month where each day show as column. Static column is best for 3/5/7/10 days.... – Rahul Biswas Sep 16 '21 at 06:34
You'll get an idea from above query where conditional aggregation has been introduced. – Rahul Biswas Sep 16 '21 at 06:45
Thanks @SunilNatwadiya for accepting along with upvoting my answer. – Rahul Biswas Sep 16 '21 at 07:19

how to make pivot mysql table as follows?

1 Answers1

Linked

Related