In article <[EMAIL PROTECTED]>, Mauricio Pellegrini <[EMAIL PROTECTED]> writes:
> 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. How do you distinguish between "Hon" and "Gas"? When you delete the line with ID 1 and recreate col_type, "Gas" all of a sudden becomes "Hon". Smells like a horribly broken table design. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]