How to achieve Pivot property (or output) using mysql based on information of another table that can be updated dynamically

Question

I want to represent a column data as row in table...

I have 3 Input tables..

             Person

  Id   |   Name |  age   | Country
 ----- | :----: | :----: | :-----:
   1   |  anil  |   20   |  India
   2   |  Raggu |   21   |  India
   3   |  IRSHAD|   22   |  India
   4   |  Sravas|   20   |  India
   5   |    .   |   .    |   .
   6   |    .   |   .    |   .
   7   |    .   |   .    |   .

             Test

  Id   |   Name   |   description   
 ----- | :----:   | :-------------: 
   1   |  Height  |   Height of a person in feets   
   2   |  Weight  |   Weight of a person in kgs 
   3   |  Fat     |   Fat of a person in calories  
   4   |    .     |        .
   5   |    .     |        .
   6   |    .     |        .
   7   |    .     |        .

             Gim

 Person_Id   | Test_Id |  value   
 ---------   | :----:  | :----: 
   1         |     1   |   6 
   2         |     3   |   120  
   1         |     2   |   60   
   4         |     1   |   5.8    
   6         |     .   |   .    
   7         |     .   |   .

I need Out Table as

             Gim_Result

   Name |  Height| Weight |  Fat   |....
 :----: | :----: | :-----:|:-----: |
  anil  |   6    |   60   | null   | .....
  Raggu |   null |   null | 120    | .....
  IRSHAD|   null |  null  | null   | ....
  Sravas|   5.8  |  null  | null   | ....
   .    |    .   |   .    |   .    | .....
   .    |    .   |   .    |   .    | .....
   .    |    .   |   .    |   .    | .....

I need a query such that it will produce result as i shown above table It dynamically generate new column in Result Table for each different Test in Test Table

What i think we can generate this type of result using pivot in sql server

but i need to generate this result using mysql...

Possible duplicate of [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) — Pred, Jul 14 '17 at 06:52
in my case i don't know what fields exits in Test table and when new test added in test table my static query automatically generate output table with new test as column also. — anil_g, Jul 14 '17 at 07:01

score 1 · Accepted Answer · answered Jul 14 '17 at 06:50

1

The basic principle looks like this:

SELECT
    EntityID
  , SUM(CASE WHEN PivotColumn = 'PivotColumnValue1' THEN PivotValue ELSE NULL END)
  , SUM(CASE WHEN PivotColumn = 'PivotColumnValue2' THEN PivotValue ELSE NULL END)
  /* ... */
  , SUM(CASE WHEN PivotColumn = 'PivotColumnValueN' THEN PivotValue ELSE NULL END)
FROM
  MyTable
GROUP BY
  EntityID
;

You can of course use any aggregation function of your choice.

answered Jul 14 '17 at 06:50

Pred

8,789
3
26
46

in my case i don't know what field exits in Test table and when new test added in test table my query automatically generate output table with new test column – anil_g Jul 14 '17 at 07:00
There is no dynamic pivot solution in sql servers. If you want to implement it, you'll have to use dynamic sql. – Pred Jul 14 '17 at 08:47

How to achieve Pivot property (or output) using mysql based on information of another table that can be updated dynamically

1 Answers1