: i have one table with 4 fields (nik,name,child_name,child_number) i wan to query that the result will be like this +------+-------+--------+--------+--------+-------- + | nik | name | child1 | child2 | child3 | child4 | +------+-------+--------+--------+--------+-------- + | 1 | pai | mai | NULL | NULL | NULL | | 2 | mai | NULL | NULL | NULL | NULL | +------+-------+--------+--------+--------+-------- +
in oracle i can do this query like below select nik,name, max(decode(child_number,1,child_name)) child1, max(decode(child_number,2,child_name)) child2, max(decode(child_number,3,child_name)) child3, max(decode(child_number,4,child_name)) child4, from test group by nik,name and its work fine now i want to try in MYSQL but not work i use query like this SELECT nik, name, max(case when child_number='1' THEN child_name end) child1, max(case when child_number='2' THEN child_name end) child2, max(case when child_number='3' THEN child_name end) child3, max(case when child_number='4' THEN child_name end) child4 FROM test group by nik,name but the result is +------+-------+--------+--------+--------+-------- + | nik | name | child1 | child2 | child3 | child4 | +------+-------+--------+--------+--------+-------- + | 1 | pai | mai | NULL | NULL | NULL | | 2 | mai | NULL | NULL | NULL | NULL | +------+-------+--------+--------+--------+-------- + why ? any body know ? thank's --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
