tyuret
asked on
09 Nov 11 06 - Create a view with dynamic columns
Person
ID NAME
3 Jane
4 John
5 James
Extracolumns
ID COLNAME
6 sex
7 size
8 haircolor
Extracolumnvalue
ID EXCOLID PERID VALUE
1 6 3 female
2 7 3 medium
3 7 4 large
And I need a dynamic view which gives me the below (note that extracolumns table can have more rows)
PERSONID PERSONNAME sex size haircolor
3 Jane female medium null
4 John null large null
5 James null null null
Can anybody help me? Thank you very much
ID NAME
3 Jane
4 John
5 James
Extracolumns
ID COLNAME
6 sex
7 size
8 haircolor
Extracolumnvalue
ID EXCOLID PERID VALUE
1 6 3 female
2 7 3 medium
3 7 4 large
And I need a dynamic view which gives me the below (note that extracolumns table can have more rows)
PERSONID PERSONNAME sex size haircolor
3 Jane female medium null
4 John null large null
5 James null null null
Can anybody help me? Thank you very much
For a more generalized solution on how to make a pivot table (crosstab) with MySQL, see http://www.artfulsoftware.com/infotree/queries.php#78
ASKER
Hi thank you for the response.
But please note that the field sex, size, haircolor are also dynamic, (I had mentioned this detail in the question)
Is it possible to modify to support this?
But please note that the field sex, size, haircolor are also dynamic, (I had mentioned this detail in the question)
Is it possible to modify to support this?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I use PHP, but I would prefer to do it as a whole in MySQL because of performance issues
I don't think using variable column names would affect the performance much. You still have to get the desired column names from the input, and you are essentially doing it all in MySQL this way.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Code can be used to dynamically set the column headings in the crosstab.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select p.id personid, p.name personname,
group_concat(if(ecv.excoli
group_concat(if(ecv.excoli
group_concat(if(ecv.excoli
FROM Person p, Extracolumnvalue ecv
WHERE ecv.perid=p.id
group by p.id