0

Note : I cannot change Table structure

These are the three tables- user,user_field_type and user_field_value which needs to be joined and produce the result shown below

user

User_code    User_id
-----------------
test11      000_1
test12      000_2
test13      000_3
test14      000_5

user_field_type 

field_name   field_id
----------------------
Name         100_1
Age          100_2
Class        100_3
Roll         100_4

User_field_value

user_id    field_id    field_value
-------------------------------------
000_1      100_1       Tom
000_1      100_2       6
000_1      100_3       2
000_1      100_4       1
000_2      100_1       Dick
000_2      100_2       6
000_2      100_3       2
000_2      100_4       2
000_3      100_1       Harry
000_3      100_2       6
000_3      100_3       2
000_3      100_4       3

Result Needed:

user_id   user_code  Name    Age    Class  Roll
------------------------------------------------
000_1     test11      Tom      6        2     1
000_2     test12      Dick     6        2     2
000_3     test13      Harry    6        2     3

I tried Group concat which doesn't produce desired results and i'm afraid of hard coding columns as it will require changes everytime we add a new user field type

and_and
  • 1
  • 1
    MySql does not provide any pivot functionality, so you will have to hardcode the column names. – forpas Aug 11 '21 at 09:59
  • Does this answer your question? [MySQL - Rows to Columns](https://stackoverflow.com/questions/1241178/mysql-rows-to-columns) – Janez Kuhar Aug 11 '21 at 10:04

0 Answers0