Hopefully the scrip I created below is more helpful. It creates the sample data and the wanted results also in another table. See scrip below.
Thanks
CREATE TABLE [dbo].[table1](
Participant_ID [int] NOT NULL,
[Supporter_ID] [int] NULL,
[Event_Code] [varchar](50) NOT NULL,
[Event_Role] [varchar](20) NULL,
[IsCoordinator] [smallint] NOT NULL,
[Speaker] [varchar](20) NULL,
[IsVolunteer] [smallint] NOT NULL,
[IsInstructor] [smallint] NOT NULL,
[Attended_INT] [smallint] NOT NULL,
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[wanted_results](
Participant_ID [int] NOT NULL,
[Supporter_ID] [int] NULL,
[Event_Code] [varchar](50) NOT NULL,
[Event_Role] [varchar](20) NULL,
[IsCoordinator] [smallint] NOT NULL,
[Speaker] [varchar](20) NULL,
[IsVolunteer] [smallint] NOT NULL,
[IsInstructor] [smallint] NOT NULL,
[Attended_INT] [smallint] NOT NULL,
) ON [PRIMARY]
Go
--example data
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('1894','0013','ER11','NULL','0','-1','0','0','-1');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('2893','0013','JR03','NULL','0','-1','-1','0','-1');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('1894','0013','ER11','NULL','0','-1','0','0','-1');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('2893','9930','JR03','NULL','0','-1','0','0','-1');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('4611','2754','SR05','NULL','-1','0','0','0','0');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('9009','8624','LL08','NULL','-1','0','-1','0','-1');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('1893','2010','CC11','NULL','0','0','0','0','0');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('4629','5285','SR05','NULL','-1','0','0','0','0');
GO
--wanted results is created by inserting data into the 'Event_Role' column where the 5 last columns equals -1.
INSERT INTO wanted_results (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('1894','0013','ER11','Speaker','0','-1','0','0','-1');
INSERT INTO wanted_results (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('1894','0013','ER11','Attended_INT','0','-1','0','0','-1');
INSERT INTO wanted_results (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('2893','0013','JR03','Speaker','0','-1','-1','0','-1');
INSERT INTO wanted_results (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('2893','0013','JR03','Attended_INT','0','-1','-1','0','-1');
INSERT INTO wanted_results (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('2893','0013','JR03','IsVolunteer','0','-1','-1','0','-1');
INSERT INTO wanted_results (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('1894','0013','ER11','Speaker','0','-1','0','0','-1');
INSERT INTO wanted_results (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('1894','0013','ER11','Attended_INT','0','-1','0','0','-1');
INSERT INTO wanted_results (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('2893','9930','JR03','Speaker','0','-1','0','0','-1');
INSERT INTO wanted_results (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('2893','9930','JR03','Attended_INT','0','-1','0','0','-1');
INSERT INTO wanted_results (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('4611','2754','SR05','IsCoordinator','-1','0','0','0','0');
INSERT INTO wanted_results (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('9009','8624','LL08','IsCoordinator','-1','0','-1','0','-1');
INSERT INTO wanted_results (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('9009','8624','LL08','IsVolunteer','-1','0','-1','0','-1');
INSERT INTO wanted_results (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('9009','8624','LL08','Attended_INT','-1','0','-1','0','-1');
INSERT INTO wanted_results (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('1893','2010','CC11','NULL','0','0','0','0','0');
INSERT INTO wanted_results (Participant_ID,Supporter_ID,Event_Code,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT) VALUES ('4629','5285','SR05','IsCoordinator','-1','0','0','0','0');