-1
user_id    meta_key  meta_value
1          name      abc
1          email     abc@gmail.com
1          phone     123
1          age       22
2          name      pqr
2          phone     456
3          name      xyz
3          email     xyz@gmail.com
3          phone     789
4          name      zzz

I have wordpress database saving user values as above but wile exporting this information to excel file, i want to format data as below.

user_id name    email           phone   age
1       abc     abc@gmail.com   123     22
2       pqr                     456 
3       xyz     xyz@gmail.com   789 
4       zzz

How it can be achieved?

Brijesh
  • 91
  • 1
  • 14
ganga v
  • 1
  • 1
  • 1
    you need to show us what you have attempted so far. – Ray A Jul 25 '19 at 13:07
  • Please check [this](https://stackoverflow.com/questions/5337380/converting-columns-into-rows-with-their-respective-data-in-sql-server) one – Brijesh Jul 25 '19 at 13:46
  • SELECT user_id , CASE WHEN (meta_key='nickname') THEN meta_value ELSE NULL END AS Name, CASE WHEN (meta_key='_cand_dob') THEN meta_value ELSE NULL END AS Date_Of_Birth, CASE WHEN (meta_key='_cand_gender') THEN meta_value ELSE NULL END AS Gender FROM wp_usermeta GROUP BY user_id But its, returning null values for all columns except Name column. – ganga v Jul 26 '19 at 10:48

1 Answers1

0

I would suggest using PIVOT and UNPIVOT. You can read about it here.

You could write a simple SELECT...CASE statement, but generally this way is better and more readable. I would suggest you try writing the code yourself, but if there is something you do not understand, hit me up in the comments.

Martin Dimitrov
  • 1,304
  • 1
  • 11
  • 25
  • Hello, I have followed SELECT.. CASE condition. rows and columns are coming in expected result set but with null values though there are values in database. – ganga v Jul 29 '19 at 05:13
  • @gangav Have you tried the `PIVOT` statement? It is far easier and more readable, which makes it easier debug-able if there are issues like this `NULL` one. – Martin Dimitrov Jul 29 '19 at 06:47
  • Yes. I tried that as well but that is saying syntax error. SELECT user_id, nickname, _cand_dob, _cand_gender FROM (SELECT user_id, meta_key, meta_value FROM wp_usermeta ) AS Tab1 , PIVOT (SUM (meta_value) FOR meta_key IN (nickname,_cand_dob,_cand_gender ) )AS Tab2 ORDER BY Tab2.user_id – ganga v Jul 29 '19 at 08:17
  • @gangav I cannot write you the query right now, but on the first look, there is an extra comma after Tab1. `PIVOT` is not in the `SELECT` clause, but a difference clause altogether. Try to run the query without the extra comma between Tab1 and `PIVOT` and give me the result – Martin Dimitrov Jul 29 '19 at 08:46