MySQL: select statement with Dynamic columns

Question

I am stuck to create dynamic column from select query,

Name   ID   ROLE_START  ROLE              DEPT

Peter   271 2016-04-15  Jr. Developer      Dev
Peter   271 2015-10-13  Trainee            Dev
Thomas  596 2019-05-20  Jr. Tester         Testing
Thomas  596 2019-02-18  Trainee            Testing
Ablish  070 2011-05-02  Artist             Designer
Adarsh  429 2018-04-01  Jr. Data Scientist Data
Adarsh  429 2018-06-01  Data Scientist     Data

My Required out put is,

Name   ID   201105_ROLE 201105_DEPT 201510_ROLE   201510_DEPT 201604_ROLE   201604_DEPT  201902_ROLE 201902_DEPT
Peter  271  Trainee       Dev         Jr. Developer Dev 
Thomas 596                                                       Trainee       Testing       Jr. Tester Testing

... ...

etc

my query was,

select
        emp_id,
        nickName,
        if(SUBSTRING_INDEX(SUBSTRING_INDEX(comp_roles, '200801:', -1), ',', 1) like "20%", null, SUBSTRING_INDEX(SUBSTRING_INDEX(comp_roles, '200801:', -1), ',', 1)) as `200801`,
...
from employee e
join role_data rd on rd.empID = r.empID
group by r.empID

is they any better method to do so?

Consider handling issues of data display in the presentation layer/application-level code, assuming you have that (e.g. a simple PHP loop acting upon an ordered array). — Strawberry, Dec 03 '19 at 14:28
am creating sql script. Store procedure also fine. so no need of PHP in my case — Mohideen bin Mohammed, Dec 03 '19 at 14:29
Tip: `EXTRACT(YEAR_MONTH FROM role_start)`. See https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_extract — Bill Karwin, Dec 03 '19 at 16:09
Also see my answer here: https://stackoverflow.com/questions/25594902/row-to-column-transformation-in-mysql/25594969#25594969 — Bill Karwin, Dec 03 '19 at 16:10
@Strawberry i have done this using python. found there is no other way apart from structuring data using external scripts. thanks — Mohideen bin Mohammed, Dec 06 '19 at 05:23

MySQL: select statement with Dynamic columns

0 Answers0