pivot dynamic number rows into columns without grouping

Question

I have a task table with following records:

id  |  client_id  |  user_id  |  designation_id   |  total_hours
-----------------------------------------------------------------
1   |  14         |  134      |  1                |  4
2   |  14         |  135      |  2                |  1
3   |  15         |  136      |  3                |  3
4   |  16         |  137      |  4                |  4.5

And designation table:

id  |  title
------------------------
1   | Cloud Analyst
2   | Data Analyst
3   | Data QA Analyst
4   | Project Manager
.
.

Designation records are dynamic and so I don't want to hard-code them.

I am trying to get following output:

client  |  user  |  Cloud Analyst  |  Data Analyst  |  Data QA Analyst  |  Project Manager
-------------------------------------------------------------------------------------------
14      |  134   |  4              |                |                   |          
14      |  135   |                 |  1             |                   |
15      |  136   |                 |                |   3               |
16      |  137   |                 |                |                   |   4.5

In words:

list all available designations (from designation table) as columns in addition to client_id, and designation_id columns from task table.
total_hours values should be placed under respective designation columns, .i.e, if user is cloud analyst (by designation_id) his hours value should be under cloud analyst column.

Here's how I tried to do:

SELECT t.client_id, t.user_id, 
(case  
       when d.id = t.designation_id then t.total_hours 
       else '' 
       end as d.title)
FROM task t
INNER JOIN designations d
on d.id = t.designation_id
ORDER BY client_id ASC

How can I achieve the output?

score 0 · Answer 1 · answered Jul 19 '19 at 16:26

0

If you need pivot you could use several case (or if)

SELECT t.client_id
    , t.user_id
    , case when t.designation_id = 1 then t.total_hours else null end `Cloud Analyst` 
    , case when t.designation_id = 2 then t.total_hours else null end `Data Analyst` 
    , case when t.designation_id = 3 then t.total_hours else null end `Data QA Analyst` 
    , case when t.designation_id = 4 then t.total_hours else null end `Project Manager` 
from  task t 
order by  client_id ASC

answered Jul 19 '19 at 16:26

ScaisEdge

131,976
10
91
107

i've updated my post on designation rows being dynamic and so want to hard-code them. – Azima Jul 19 '19 at 16:38
mysql have not features for dinamic pivot .. if you need a dinamic solution you must work server side for build dinamic sql or manage the situation at presentation level .. – ScaisEdge Jul 19 '19 at 16:40

score 0 · Answer 2 · answered Jul 19 '19 at 16:28

SELECT t.client_id
     , t.user_id
     , d.title
     , t.total_hours 
  FROM task t 
  JOIN designation d 
    ON d.id = t.designation_id;
+-----------+---------+-----------------+-------------+
| client_id | user_id | title           | total_hours |
+-----------+---------+-----------------+-------------+
|        14 |     134 | Cloud Analyst   |        4.00 |
|        14 |     135 | Data Analyst    |        1.00 |
|        15 |     136 | Data QA Analyst |        3.00 |
|        16 |     137 | Project Manager |        4.50 |
+-----------+---------+-----------------+-------------+

The rest of this problem is best resolved in application code

pivot dynamic number rows into columns without grouping

2 Answers2