1

I´m trying to denormalize some data in a table, but I cannot do it because I do not find the way to do it right way in mysql.

Table:person_attribute 

Attribute_ID Attribute
------------  ---------
1            Person Name
2            Person Age
3            Person Gender
.
.
.
34           Phone Number

34 attributes as if now, but it is subject to change. i.e i may get additional attributes also.

Table:person_data

Person ID fk_Attribute_ID Attribute_Value
--------- --------------- -------------
1          1              Max
1          2              55
1          3              male  
2          1              John
2          2              20
2          3              male

Excepted Output:

Person ID Person Name Person Age Person Gender
--------- ----------- ---------- -------------
1           Max           55          male
2           john          20          male

My solution:

Select 
Person ID, 
case when fk_Attribute_ID = ( select Attribute_ID from    person_attribute where Attribute_ID = 1) then Attribute_Value end as Person Name,case when fk_Attribute_ID = ( select Attribute_ID from    person_attribute where Attribute_ID = 2) then Attribute_Value end as Person Age,case when fk_Attribute_ID = ( select Attribute_ID from    person_attribute where Attribute_ID = 3) then Attribute_Value end as Person Gender From person_attribute left join on person_data (Attribute_ID  = fk_Attribute_ID)

Person ID Person Name Person Age Person Gender
--------- ----------- ---------- -------------
1           Max           null          null
1           null          55            null
1           null          null          male
2           john          null          null
2           null          20            null
2           null          null          male

Please help me with excepted output. Thanks

MAX
  • 1,562
  • 4
  • 17
  • 25

1 Answers1

1

It's a good idea to do this. This is not even denormalization; the resulting table is still normalized (i.e. no redundance etc.).

What you want is an aggregated row per person:

select 
  person_id,
  max(case when fk_attribute_id = 1 then attribute_value end) as person_name,
  max(case when fk_attribute_id = 2 then attribute_value end) as person_age,
  ...
from person_data
group by person_id;

Of course you need to know all attributes to build this query.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • Hi Thorsten, Thanks for the Answer. one clarification what is the use of Max function here ? max function is applied on which column here. fk_attribute_id or attribute_value ? – MAX Sep 19 '16 at 10:39
  • Is that to ignore null records and consider only the not null record ? – MAX Sep 19 '16 at 10:41
  • There are many rows per person. When aggregating, you must say which value of all those rows you want. You need an aggregation function (`min`, `max`, etc.) to do so. Yes, `max` ignores nulls, so it gets you the one existing entry (provided there is one) - or the maximum in case there happen to be more than one entry for one person and attribute mistakenly. – Thorsten Kettner Sep 19 '16 at 10:48
  • Ok Nice. One more question, I have 400 + tables in mysql database. i want to know to which table a column belongs, if i pass column name as filter value. – MAX Sep 19 '16 at 12:34
  • See here: http://stackoverflow.com/questions/1526688/get-table-column-names-in-mysql. – Thorsten Kettner Sep 19 '16 at 13:51