We help IT Professionals succeed at work.

09 Nov 11 06 - Create a view with dynamic columns

tyuret
tyuret asked
on
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
Comment
Watch Question

BRONZE EXPERT

Commented:
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
BRONZE EXPERT

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

Author

Commented:
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?
Commented:
What language are you working in?  If you have php, then you can set a variable for the column name, so for example,

$col1 = 'sex';
$col2 = 'haircolor';
(presumably from some input form)

and then

mysql_query("SELECT $col1, $col2 FROM Table ...");

Author

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

Commented:
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.  
Retired IT Professional
BRONZE EXPERT
Commented:
Comment in access: (idea)- modify to your environment.

table: a_p  your person table
p_id      p_name
3      Jane
4      John
5      James

Table: a_ec your Extracolumns table
ec_id      ec_colname
6      sex
7      size
8      haircolr

table: a_ecv your Extracolumnvalue table
ecv_id      ecv_excolid      ecv_perid      ecv_value
1      6                    3      female
2      7                    3      medium
3      7                    4      large

Creosstab to produce the result:

TRANSFORM First(a_ecv.ecv_value) AS FirstOfecv_value
SELECT a_p.p_id AS personid, a_p.p_name AS personname
FROM (a_ecv LEFT JOIN a_ec ON a_ecv.ecv_excolid = a_ec.ec_id) RIGHT JOIN a_p ON a_ecv.ecv_perid = a_p.p_id
GROUP BY a_p.p_id, a_p.p_name
PIVOT a_ec.ec_colname;

Result: (= sign used for null values to align columns.
personid      personname <>      sex      size
3             Jane      ==        female      medium
4             John      ==        ===        large
5             James    ==        ===       ===      
Hamed NasrRetired IT Professional
BRONZE EXPERT

Commented:
Code can be used to dynamically set the column headings in the crosstab.
BRONZE EXPERT
Commented:
Most of the time, this is done as a two step process. The first time you get the field names. The next time you construct the query.

See: http://dev.mysql.com/tech-resources/articles/wizard/

Explore More ContentExplore courses, solutions, and other research materials related to this topic.