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

mankowitz

You want something like this:

select p.id personid, p.name personname,
group_concat(if(ecv.excolid=6,ecv.value,'')) sex,
group_concat(if(ecv.excolid=7,ecv.value,'')) size,
group_concat(if(ecv.excolid=8,ecv.value,'')) haircolor
FROM Person p, Extracolumnvalue ecv
WHERE ecv.perid=p.id
group by p.id

mankowitz

For a more generalized solution on how to make a pivot table (crosstab) with MySQL, see http://www.artfulsoftware.com/infotree/queries.php#78

tyuret

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?

SOLUTION

Cornelia Yoder

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

tyuret

ASKER

I use PHP, but I would prefer to do it as a whole in MySQL because of performance issues

Cornelia Yoder

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

Hamed Nasr

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Hamed Nasr

Code can be used to dynamically set the column headings in the crosstab.

SOLUTION

mankowitz

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer