-1

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
Slava Rozhnev
  • 9,510
  • 6
  • 23
  • 39
Anju NK
  • 1
  • 2

1 Answers1

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

Slava Rozhnev
  • 9,510
  • 6
  • 23
  • 39
  • please can you help me out in above query, how to pass the dynamic values for Activity and comments. Thanks in advance – Anju NK Oct 22 '20 at 07:27
  • 1
    Dynamic queries available in stored procedures. https://stackoverflow.com/questions/23178816/mysql-dynamic-query-in-stored-procedure – Slava Rozhnev Oct 22 '20 at 07:32
  • 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 – Anju NK Oct 22 '20 at 10:36
  • 1
    SQL not suitable for implement so complicated logic. Do it in your application layer. – Slava Rozhnev Oct 22 '20 at 10:42