Thanks for your response apologise for the last post I had no idea about what I was trying to do. Hopefully the second post from me is more helpful to you.
I have a table similar to the one below, where I have to use the last 6 column to populate the ‘Event Role’ based on the following higher level heirarchy – Speaker
Coordinator
Volunteer
Attendee
Registered and may more later on. For the ‘Participant_ID’ ‘Supporter_ID’ ‘Event_Code’ to be group into them.
Table1
Participant_IDSupporter_IDEvent_CodeEvent_RoleIsCoordinatorSpeakerIsVolunteerIsInstructorAttended_INTRegistered_INT
18940013ER11NULL0-100-12
28939930JR03NULL0-100-13
47044255SR05NULL-100003
46112754SR05NULL-100003
90098624LL08NULL-1000-12
218107660001NULL-1000-12
46192636SR05NULL-100003
18932010CC11NULL-100002
46295285SR05NULL-100003
218889021212NULL0-10003
90126569LL08NULL-1000-13
29023004JR03NULL0-100-13
28795589JR03NULL0-100-13
218803871112NULL0-10003
218804091212NULL0-10003
14248862ON09NULL00-1002
46785510SR05NULL-1000-13
46824546SR05NULL-100003
76653923DC07NULL-100003
218255750712NULL0-100-13
47144913SR05NULL-100003
For example in the ‘Speaker’ column is -1 in table_original it would appear as Speaker in Event_Role column in the ‘Event_Role’ and so on for the other columns (by the way 0 is NULL or nothing) Updates into Event_Role. For the for ‘Registered_INT’ column which also goes into the Event_Role Column and shows up as
‘’1= do not register
“2=NO
“3=YES
By the way a Supporter can attend multiple events so can appear multiple times in a group. It show look some like table below.
Participant_IDSupporter_IDEvent_CodeEvent_RoleIsCoordinatorSpeakerIsVolunteerIsInstructorAttended_INTRegistered_INT
18940013ER11Speaker0-100-12
28939930JR03Speaker0-100-13
46112754SR05Coordinator-100003
90098624LL08Coordinator-1000-12
18932010CC11Do Not register000001
46295285SR05Coordinator-100003
218889021212Speaker0-10003
90126569LL08Attended-1000-13
29023004JR03Speaker0-100-13
29023004JR03Attended0-100-13
28795589JR03Attended0-100-13
28795589JR03Speaker0-100-13
90126569LL08Coordinator-1000-13
218803871112NO000002
14248862ON09Volunteer00-1002
46785510SR05Coordinator-1000-13
46824546SR05Coordinator-100003
76653923DC07Coordinator-100003
218255750712YES0000-13
And help thank again! SQL Code below --
CREATE TABLE [dbo].[table1](
[Participant_ID] [int] NOT NULL,
[Supporter_ID] [int] NULL,
[Event_Code] [varchar](50) NOT NULL,
[Event_Start_Date_ID] [int] NULL,
[Attended_Y_N] [varchar](1) NULL,
[Registration_Date_ID] [int] NULL,
[Registered] [varchar](15) NULL,
[Enquiry_Date_ID] [int] NULL,
[Enquired_Y_N] [varchar](1) 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,
[Registered_INT] [smallint] NOT NULL
) ON [PRIMARY]
----Insert data into table1
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('1894','0013','ER11','20111015','Y','NULL','N','NULL','NULL','NULL','0','-1','0','0','-1','2');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('2893','9930','JR03','20031204','Y','NULL','Y','NULL','NULL','NULL','0','-1','0','0','-1','3');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('4704','4255','SR05','NULL','N','20050802','Y','NULL','NULL','NULL','-1','0','0','0','0','3');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('4611','2754','SR05','NULL','N','20050802','Y','NULL','NULL','NULL','-1','0','0','0','0','3');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('9009','8624','LL08','20080521','Y','NULL','N','NULL','NULL','NULL','-1','0','0','0','-1','2');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('2181','0766','0001','20121206','Y','NULL','N','NULL','NULL','NULL','-1','0','0','0','-1','2');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('4619','2636','SR05','NULL','N','20050802','Y','NULL','NULL','NULL','-1','0','0','0','0','3');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('1893','2010','CC11','20111129','N','NULL','N','NULL','NULL','NULL','-1','0','0','0','0','2');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('4629','5285','SR05','NULL','N','20050801','Y','NULL','NULL','NULL','-1','0','0','0','0','3');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('2188','8902','1212','20121202','N','NULL','Y','NULL','NULL','NULL','0','-1','0','0','0','3');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('9012','6569','LL08','20080521','Y','20100708','Y','NULL','NULL','NULL','-1','0','0','0','-1','3');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('2902','3004','JR03','20031204','Y','NULL','Y','NULL','NULL','NULL','0','-1','0','0','-1','3');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('2879','5589','JR03','20031204','Y','NULL','Y','NULL','NULL','NULL','0','-1','0','0','-1','3');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('2188','0387','1112','20121125','N','NULL','Y','NULL','NULL','NULL','0','-1','0','0','0','3');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('2188','0409','1212','20121209','N','20121209','Y','NULL','NULL','NULL','0','-1','0','0','0','3');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('1424','8862','ON09','NULL','N','NULL','N','NULL','NULL','NULL','0','0','-1','0','0','2');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('4678','5510','SR05','NULL','Y','20050802','Y','NULL','NULL','NULL','-1','0','0','0','-1','3');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('4682','4546','SR05','NULL','N','20050803','Y','NULL','NULL','NULL','-1','0','0','0','0','3');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('7665','3923','DC07','20070421','N','20070315','Y','NULL','NULL','NULL','-1','0','0','0','0','3');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('2182','5575','0712','20120722','Y','NULL','Y','NULL','NULL','NULL','0','-1','0','0','-1','3');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Start_Date_ID,Attended_Y_N,Registration_Date_ID,Registered,Enquiry_Date_ID,Enquired_Y_N,Event_Role,IsCoordinator,Speaker,IsVolunteer,IsInstructor,Attended_INT,Registered_INT) VALUES ('4714','4913','SR05','NULL','N','20050803','Y','NULL','NULL','NULL','-1','0','0','0','0','3');