Pivoting query based on dynamic columns data using MySQL [duplicate]

Question

have one table called A and the data is like below:

TaskId  Activity TestData
111     ACT1     X
111     ACT2     Y
111     ACT3     Z

have one more table called B and data like below:

Activity   Comments
ACT1       CM1
ACT2       CM2
ACT3       CM3

need the result to be displayed like below , in which comments column is dynamic data.

TaskId  ACT1  Comments  ACT2   Comments  ACT3   Comments
111     X     CM1       Y      CM2       Z      CM3

MySQL is really bad at doing that. You should do that in your program logic if possible. — juergen d
– juergen d, Commented Oct 20, 2020 at 13:31
As mentioned, seriously consider handling issues of data display in application code — Strawberry
– Strawberry, Commented Oct 20, 2020 at 15:22

Slava Rozhnev · Accepted Answer · 2020-10-20 14:22:24Z

0

This is SQL pivoting query:

SELECT
    TaskId,
    GROUP_CONCAT(IF(A.Activity='ACT1', TestData, null)) ACT1,
    GROUP_CONCAT(IF(A.Activity='ACT1', Comments, null)) Comments1,
    GROUP_CONCAT(IF(A.Activity='ACT2', TestData, null)) ACT2,
    GROUP_CONCAT(IF(A.Activity='ACT2', Comments, null)) Comments2,
    GROUP_CONCAT(IF(A.Activity='ACT3', TestData, null)) ACT3,
    GROUP_CONCAT(IF(A.Activity='ACT3', Comments, null)) Comments3
FROM A
LEFT JOIN B ON A.Activity = B.Activity
GROUP BY TaskId
;

Looks SQL fiddle

answered Oct 20, 2020 at 14:22

Slava Rozhnev

10.2k6 gold badges27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Anju NK Over a year ago

please can you help me out in above query, how to pass the dynamic values for Activity and comments. Thanks in advance

Slava Rozhnev Over a year ago

Dynamic queries available in stored procedures. stackoverflow.com/questions/23178816/…

Anju NK Over a year ago

please help me, I want to pass comma separated values for ID to fetch data using while loop and combine all the table result which i am getting from while loop result inner join with main table and get it all in one table data. thanks in advance

Slava Rozhnev Over a year ago

SQL not suitable for implement so complicated logic. Do it in your application layer.

Collectives™ on Stack Overflow

Pivoting query based on dynamic columns data using MySQL [duplicate]

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related