-1

I am writing a query to get result from a table "tbl_meta" having 3 columns as ID,attr_name,attr_value.

Table structure is like 2nd column is name of attribute and 3rd column is its value like below 

ID     attr_name     attr_value

1      name      abc

2      address   aaa 

3      age       25

So,when I use "select * from tbl_meta where ID = 10" , I got result like as it is as shown above but I need result with columns in a single row like below:

col1    col2    col3    col4      col5   col6

name    abc     address  aaa      age    25

how can I do this using mysql?

NOTE

I have tried solutions given @ MySQL - Rows to Columns and MySQL pivot row into dynamic number of columns but it's not how I am expecting. When I tried like

SELECT 
case when attr_name = 'name' then attr_value  end  as name ,
case when attr_name = 'address' then attr_value  end  as address,        
case when attr_name = 'age' then attr_value  end  as   agep,

 FROM tbl_meta  
where ID = 10

I am getting output as :

name     address    age
abc      NULL       NULL
NULL     aaa        NULL
NULL     NULL       25

instead can't I get like 

 name    address    age
 abc      aaa        25

from above table.

Nil
  • 5
  • 6
  • This operation is called pivoting and has been asked and answered here on SO sooooo many times. The linked duplicate topics explain how to create both static and dynamic pivot tables via sql. Pls note that it may be a lot more efgicient to perform such transformation in the application logic as opposed to mysql. – Shadow Oct 31 '17 at 06:50
  • I have tried it before but as it's not working as per my expected result so I posted new question. – Nil Oct 31 '17 at 09:38
  • The answer to the updated question is still in the `MySQL - Rows to Columns` duplicate question. There the most upvoted answer does provide a step-by-step description of how to this transformation, pls do read it. – Shadow Oct 31 '17 at 11:13
  • @shadow Thanks , I have tried other solutions , its my fault. I will try the most upvoted solution and from onwards while posting question I will check first :-( – Nil Oct 31 '17 at 11:38
  • Hi that solution is working for me , but partially as according to 3rd step , I am getting correct value for those columns who having int data but it returns 0 for columns having string/text data. Instead of 0 it should return actual string data, so how to get rid of it? – Nil Nov 02 '17 at 06:56
  • I hope you are using your judgement and not trying to sum up a non-numeric field... See the considerations sections in the most upvoted answer. – Shadow Nov 02 '17 at 09:30
  • @shadow , I have solved that issue by using group_concat for text fields. :-) – Nil Nov 02 '17 at 10:26

1 Answers1

0

You can use something like;

SELECT *, (IF ID=10) AS new_column FROM tbl_meta;
Oguz Ozcan
  • 1,497
  • 13
  • 21