Re: multi crosstab

[Rodrigo Aliste P.]

I think I acompplish it!

Merge that query into a view called report_1 and then:

(1) SELECT user_id, MAX(IF(name='RUT',value,NULL)) AS 'Rut',
MAX(IF(name='Name',value,NULL)) AS 'Name' FROM report_1 GROUP BY user_id;

+---------+-----------+--------+
| user_id | Rut       | Name   |
+---------+-----------+--------+
|       1 | 170332881 | NULL   |
|       2 | 456465789 | felipe |
+---------+-----------+--------+

Then:

INSERT INTO data(user_id,field_id,value) values (1, 1, 'Rodrigo');

And then (1):

+---------+-----------+---------+
| user_id | Rut       | Name    |
+---------+-----------+---------+
|       1 | 170332881 | Rodrigo |
|       2 | 456465789 | felipe  |
+---------+-----------+---------+

:)


2009/4/5 Rodrigo Aliste P. <raliste@stripped>

> Hi Devs
>
> I have a tricky question and I want to know if its possible to accomplish
> it on mysql and how.
>
> The following query:
>
> SELECT
>     fields.id AS field_id,
>     data.user_id,
>     fields.type,
>     fields.name,
>     data.value,
>     entries.ip,
>     entries.date,
>     entries.user_agent
> FROM
>     data,
>     fields,
>     entries,
>     forms
> WHERE
>     data.field_id = fields.id AND
>     data.user_id = entries.id AND
>     forms.id = fields.form_id AND
>     forms.id = entries.form_id AND
>     forms.id = 1
> ORDER BY
>     user_id, field_order
>
> Returns this:
>
>
>
> +----------+---------+------+--------+-----------+-----------+---------------------+------------------+
> | field_id | user_id | type | name   | value     | ip        |
> date                | user_agent       |
>
>
> +----------+---------+------+--------+-----------+-----------+---------------------+------------------+
> |        2 |       1 | text | RUT    | 17881     | 127.0.0.1 | 2009-03-08
> 14:27:51 | Unit Check       |
> |        1 |       2 | text | Name   | felipe    | 127.0.0.2 | 2009-03-08
> 14:28:35 | Mozilla          |
> |        2 |       2 | text | RUT    | 456465789 | 127.0.0.2 | 2009-03-08
> 14:28:35 | Mozilla          |
>
>
> +----------+---------+------+--------+-----------+-----------+---------------------+------------------+
>
> And I want to display this by field_id and user_id like this:
>
> user_id    RUT             Name
> 1          17881
> 2          456465789       felipe
>
> I know that if would be just one reference would be easy to do (by doing
> IF(field_id=X,value,'')), I've also found the solution by application side
> (by parsing columns first, rows later),
> but it comes to troubles when I add a new fieldID or when a fieldID for
> that userID its not found.
>
> Any idea?
>
> Thanks,
>
> Rod
>
>


-- 
Rodrigo