Expand UserMeta table to flat view (rows to columns)

S

Example UserMeta table.

+--------+----------------+-------------------+
| UserID | Name           | Value             |
+--------+----------------+-------------------+
|      3 | FirstName      | FIRSTNAME276      |
|      5 | FamilyName     | FAMILYNAME885     |
|      3 | PatronymicName | PATRONYMICNAME461 |
|      2 | FirstName      | FIRSTNAME387      |
|      4 | FamilyName     | FAMILYNAME818     |
|      2 | PatronymicName | PATRONYMICNAME710 |
|      5 | PatronymicName | PATRONYMICNAME109 |
+--------+----------------+-------------------+

I wondered whether it is possible to make this as flat view by mysql view (or procedure, function, or trigger) for easy building queries. I want to see this zoo above as:

+--------+-----------+-----------+-----------+-----------+
| UserID | MetaName1 | MetaName2 | MetaName3 | .... | MetaNameN |
+--------+-----------+-----------+-----------+-----------+
....
+--------+-----------+-----------+-----------+-----------+

Any advice?

S

Smart people have prompted that is called EAV (Entity-attribute-value) model.

yu_tang

Hi @S,
EAV... this is the first I've heard about it.　Interesting for me.
Anyway, Google suggested below link to me.
http://www.artfulsoftware.com/infotree/queries.php#78
As you may already know, but just to let you know.

S

@yu_tang
YES! This link has helped me very much. Many thanks.
Didnt reliaze about it (googgle gives me nothing usefull on "eav + transpose", etc).

And yes UserMeta table it is EAV model structure, but not fully normalized.
You can read more about EAV at wikipedia.org Entity-attribute-value model

I already write "rows to columns" procedure for UserMeta
Here it is:

CREATE PROCEDURE `FlatMeta`() LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' 

BEGIN

select group_concat(Name separator ',') from (select distinct Name from GDN_UserMeta) as t INTO @Fields;
set @SqlGroups = 'UserID';
T1:
LOOP
	set @Column = substring_index(@Fields, ',', 1);
	set @Fields = mid(@Fields, length(@Column)+2);
	set @SqlGroups = concat(@SqlGroups, ', group_concat(if(Name = "', @Column, '", Value, NULL)) as ', @Column);
	if (length(@Fields) = 0) then leave T1; end if;
end loop T1;
set @Sql = concat('select ', @SqlGroups, ' from Gdn_UserMeta group by UserID');
/*select @Sql;*/
set @Sql = concat('create or replace view Gdn_VwUserMeta2 as ', @Sql);
prepare St FROM @Sql;
execute St;

END

And there are some problems:
1) We must call this every time when new Attribute 'Name' appeared in table.
2) Building part of query looks scary, construction select group_concat(Name separator ',') from (select distinct Name from GDN_UserMeta) + string functions.
3) Table Prefix fixed in procedure body.