0

There are many questions available, and I explored several such questions in SO, but none helped.

In question - https://stackoverflow.com/questions/1241178/mysql-rows-to-columns/56670844#56670844 In step -3 - They have itemvalue (mine meta_value) as int, but in my case these are varchar, so I cannot really use the sum function.

Database: MySQL

My raw data

umeta_id    user_id meta_key    meta_value
    1   1   nickname    kp
    2   1   first_name  Kumar
    3   1   last_name   Prasad
    4   1   description hello
    5   2   nickname    rg
    6   2   first_name  raghav
    7   2   last_name   gupta
    8   2   description welcome

My required selected data

user_id   nickname  first_name last_name   description
1         kp        kumar      prasad      hello
2         rg        raghav     gupta       welcome

My unsuccessful attempt as follows:

SELECT user_id, 
case when meta_key='nickname' then meta_value else null end as nickname,
case when meta_key='first_name' then meta_value else null end as first_name,
case when meta_key='last_name' then meta_value else null end as last_name,
case when meta_key='description' then meta_value else null end as description
from user_table GROUP BY user_id;

user_id nickname    first_name  last_name   description
1   kp [NULL]   [NULL]  [NULL]
2   rg  [NULL]  [NULL]  [NULL]
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Raghavendra Gupta
  • 355
  • 1
  • 2
  • 15
  • Before you wrote that the duplicate does not helpyou, please read step 3 and the considerations sections in the accrpted answer carefully! – Shadow Sep 27 '21 at 06:28
  • In step 3 - create view history_itemvalue_pivot as ( select hostid, sum(A) as A, sum(B) as B, sum(C) as C from history_extended group by hostid ); select * from history_itemvalue_pivot; +--------+------+------+------+ | hostid | A | B | C | +--------+------+------+------+ | 1 | 10 | 3 | NULL | | 2 | 9 | NULL | 40 | +--------+------+------+------+ That question's itemvalue are integers and hence he did the sum. But in my case it is string/varchar. – Raghavendra Gupta Sep 27 '21 at 06:37
  • This is fixed by this,. select user_id, GROUP_CONCAT(nickname) as nickname, GROUP_CONCAT(first_name) as first_name, GROUP_CONCAT(last_name) as last_name from (select wu.*, case when meta_key = "nickname" then meta_value end as nickname, case when meta_key = "first_name" then meta_value end as first_name, case when meta_key = "last_name" then meta_value end as last_name from user_table wu) x group by user_id ; – Raghavendra Gupta Sep 27 '21 at 06:51
  • 1
    This is why I told you to read the considerations section as well. Btw, a max() may work better for you. – Shadow Sep 27 '21 at 08:03

0 Answers0