mysql - Group sum of a column and insert -

i have unique situation here. sorry of question naive or unclear.

i have table ( called table1 ) looks this:

student first name | last name  |  id     | test name   | result  anthony            | davis      | 12353   | abc_1_1    | pass chris              | tucker     | 23412   | abc_1_3    | fail anthony            | davis      | 12355   | abc_2_4    | fail anthony            | davis      | 12356   | abc_2_1    | pass anthony            | davis      | 12635   | abc_1-5    | fail anthony            | davis      | 12375   | abc_2_3    | pass anthony            | davis      | 12935   | abc_1_8    | fail chris              | tucker     | 23341   | abc_1_2    | pass chris              | tucker     | 23541   | abc_2_3    | pass chris              | tucker     | 23431   | abc_1_4    | fail chris              | tucker     | 21341   | abc_2_1    | pass chris              | tucker     | 32341   | abc_1_6    | fail david              | steel      | 34352   | abc_2_3    | fail david              | steel      | 34352   | abc_1_2    | pass david              | steel      | 34352   | abc_2_4    | fail david              | steel      | 34352   | abc_1_1    | pass david              | steel      | 34352   | abc_1_7    | fail 

the table (table2) trying achieve :

name         | test type | pass | fail  anthonydavis | type_1    | 1    | 2 anthonydavis | type_2    | 2    | 1 christucker  | type_1    | 1    | 2 christucker  | type_1    | 1    | 2 davidsteel   | type_1    | 2    | 1 davidsteel   | type_1    | 0    | 1 

is possible achieve sql? primary key table2 (name,test type) or easier have temporary table collect data needed , transfer table2?

assuming testtype values based on first 5 characters of testname, should able use similar select data:

-- insert table2 (name, testtype, pass, fail) select concat(studentfirstname, lastname) name,   case left(testname, 5)       when 'abc_1' 'type_1'       when 'abc_2' 'type_2'     end testtype,   sum(case when result = 'pass' 1 else 0 end) pass,   sum(case when result = 'fail' 1 else 0 end) fail table1 group concat(studentfirstname, lastname),    case left(testname, 5)       when 'abc_1' 'type_1'       when 'abc_2' 'type_2'     end; 

see sql fiddle demo. once have data in format want, can insert table2.

if assumption how testtype not correct, please provide details on how determine this.

note, mysql allows alias select used in group by:

-- insert table2 (name, testtype, pass, fail) select concat(studentfirstname, lastname) name,   case left(testname, 5)       when 'abc_1' 'type_1'       when 'abc_2' 'type_2'     end testtype,   sum(case when result = 'pass' 1 else 0 end) pass,   sum(case when result = 'fail' 1 else 0 end) fail table1 group name, testtype;