Wow, that's simply magic!!!
You couldn't imagine how many diferent things I've tried
to solve this problem..
And when I thought it was impossible ...your solution worked
just fine at once!
God bless experienced people!!
The reason for trying to do such a weird thing on col_type
is that a needed something to use as a pivot condition
I'll explain a little further; thanks to you now, I have this in table
tbl
---------------------------------------
Id xorder item value col_type
---------------------------------------
1 3 15 0 1
2 3 15 5 2
3 3 15 0 3
4 8 22 7 1
5 8 22 0 2
6 10 64 20 1
---------------------------------------
Then I can run this query against tbl
SELECT xorder, item,
if( col_type=1,value , 0 ) as 'Hon',
if( col_type=2,value , 0 ) as 'Gas',
if( col_type=3,value , 0 ) as 'Other'
FROM tbl
GROUP BY xorder, item
Which would deliver this final result
------------------------------------
xorder item Hon Gas Other
------------------------------------
3 15 0 5 0
8 22 7 0 0
10 64 20 0 0
------------------------------------
There would never be more than three rows for each xorder,item group
but, of course there could be less.
That's it.
Thank you very much for your valuable help
Mauricio
On Fri, 2005-05-13 at 06:22, Harald Fuchs wrote:
> In article <[EMAIL PROTECTED]>,
> Mauricio Pellegrini <[EMAIL PROTECTED]> writes:
>
> > This is the table I have
>
> > Column Id is primary key and auto_numeric
>
>
> > -----------------------------------------
> > Id order item value col_type
> > -----------------------------------------
> > 1 3 15 0 null
> > 2 3 15 5 null
> > 3 3 15 0 null
> > 4 8 22 7 null
> > 5 8 22 0 null
> > 6 10 64 20 null
> > -----------------------------------------
>
> > And this is the result I would like to obtain.
> > Where column col_type should be filled with a number
> > representing the ordinal number for the row within the
> > group formed by order,item
>
> > -----------------------------------------
> > Id order item value col_type
> > -----------------------------------------
> > 1 3 15 0 1 <--This is row number 1 in the group
> > 2 3 15 5 2 formed by Order 3 and Item 15
> > 3 3 15 0 3
> > 4 8 22 7 1 <-This is row number 1
> > 5 8 22 0 2 <-This is row number 2
> > 6 10 64 20 1 <-This is row number 1
> > -----------------------------------------
>
> (I've renamed "order" to "xorder" since ORDER is a reserved word.)
>
> You could use the old MyISAM/AUTO_INCREMENT trick:
>
> CREATE TEMPORARY TABLE tmp (
> id INT UNSIGNED NOT NULL,
> xorder INT UNSIGNED NOT NULL,
> item INT UNSIGNED NOT NULL,
> col_type INT UNSIGNED NOT NULL AUTO_INCREMENT,
> PRIMARY KEY (xorder, item, col_type)
> ) TYPE=MyISAM;
>
> INSERT INTO tmp (id, xorder, item)
> SELECT id, xorder, item
> FROM tbl;
>
> UPDATE tbl, tmp
> SET tbl.col_type = tmp.col_type
> WHERE tbl.id = tmp.id;
>
> When you use an AUTO_INCREMENT column as the last part of a PRIMARY
> KEY, you'll get a new sequence for all combinations of the other key
> parts - but only for MyISAM tables.
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
