I think you can do this in pure SQL. Here is my attempt:
CREATE TABLE newTable (
`id` INT
, `remoteid` INT
, `value_a` INT
, `value_b` INT
, `value_c` INT
, `when` DATE
)
SELECT
`id`
`remoteid`
, MAX(IF(`type`='a', `value`, null))
, MAX(IF(`type`='b', `value`, null))
, MAX(IF(`type`='c', `value`, null))
, `when`
FROM events
GROUP BY (`id`, `remoteid`,`when`);
This is just a variation on the "pivot table" technique.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Fagyal Csongor <[EMAIL PROTECTED]> wrote on 09/03/2004 10:44:37 AM:
> Hi,
>
> I have a table that looks like this:
>
> table events:
> id : INT
> remoteid: INT
> type : enum ('a','b','c')
> value: INT
> when: DATE
>
> For each 'remoteid' and 'when', there are 3 rows with type=a,b,c
> respectively. (Bad table design that is...) I would like to shorten this
> table so it will look like this:
> id : INT
> remoteid: INT
> value_a: INT
> value_b: INT
> value_c: INT
> when: DATE
>
> My idea is to dump this table (it is a quite big table with 10M+ rows),
> write a perl script to "concatenate" corresponding 'when'+'remoateid'
> fields, and load the resulting file into another table.
>
> Just fo curiosity: can this processing be written in pure MySQL?
>
> Thank you,
> - Csongor
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
