Updated multiple rows into one column? Why is it hard? HELP

  • Trying to update a table using integer number in 3 columns and a signing texts name to results in the second table TEST2

    It only updated that last update statament which was Volunteer. It should upadte with all the other columns from TEST1.

    I even try using update with subquery but I got a error to do with multipe values. And I got no idea how to do the if else part of the sql if any other number come up.

    Thanks in advance!!

    Actual Results by using SQL Query below-

    Event_Role

    Volunteer

    Wanted Results-

    Event_Role

    Speaker

    Speaker

    Speaker

    Speaker

    coordinator

    coordinator

    coordinator

    coordinator

    Volunteer

    Volunteer

    Volunteer

    Volunteer

    Other

    Other

    Other

    CREATE TABLE [dbo].[TEST1](

    [Ispeaker] [int] NOT NULL,

    [Iscoordinator] [varchar](35) NULL,

    [Volunteer] [int] NULL,)

    ON [PRIMARY]

    GO

    Insert into TEST1 (Ispeaker,Iscoordinator,Volunteer) VALUES (-1,-1, -1);

    Insert into TEST1 (Ispeaker,Iscoordinator,Volunteer) VALUES (-1,-1, -1);

    Insert into TEST1 (Ispeaker,Iscoordinator,Volunteer) VALUES (-1,-1, -1);

    Insert into TEST1 (Ispeaker,Iscoordinator,Volunteer) VALUES (-1,-1, -1);

    Insert into TEST1 (Ispeaker,Iscoordinator,Volunteer) VALUES (-1,-1, -1);

    Insert into TEST1 (Ispeaker,Iscoordinator,Volunteer) VALUES (-1,-1, -1);

    Insert into TEST1 (Ispeaker,Iscoordinator,Volunteer) VALUES (-1,-1, -1);

    GO

    --create empty column in table2

    Select CAST(NULL AS VARCHAR(20)) AS Event_Role

    into dbo.TEST2

    UPDATE [dbo].[TEST2]

    SET Event_Role = 'Speaker'

    FROM dbo.TEST1

    Where Ispeaker = -1

    UPDATE [dbo].[TEST2]

    SET event_role = 'Coordinator'

    FROM dbo.TEST1

    WHERE IsCoordinator = -1

    UPDATE [dbo].[TEST2]

    SET event_role = 'Volunteer'

    SELECT ID

    FROM DBTEAM.azeez.Participants

    WHERE IsVolunteer = -1

    ---not sure how to create if else statment into the sql query

    UPDATE [dbo].[TEST2]

    SET event_role = 'Other'

    SELECT ID

    FROM dbo.TEST1

    WHERE ? = >2

    ------try subquery but does not work because of multipe values errors

    UPDATE [dbo].[TEST2]t

    SET event_role = 'Speaker'

    WHERE t.id IN (SELECT ID

    FROM dbo.TEST1

    WHERE IsVolunteer = -1)

    AND event_role IS NULL

    • This topic was modified 1 year, 8 months ago by  azdeji.
  • It is very unclear what you are trying to do but your update statements are full of logic errors. Let's look at them one at a time.

    UPDATE [dbo].[TEST2]

    SET Event_Role = 'Speaker'

    FROM dbo.TEST1

    Where Ispeaker = -1

    There is nothing to correlate rows from TEST2 to TEST1 so the entire table Event_Role is now Speaker.

    UPDATE [dbo].[TEST2]

    SET event_role = 'Coordinator'

    FROM dbo.TEST1

    WHERE IsCoordinator = -1

    Same as above but now the table is Coordinator.

    UPDATE [dbo].[TEST2]

    SET event_role = 'Volunteer'

    SELECT ID

    FROM DBTEAM.azeez.Participants

    WHERE IsVolunteer = -1

    This is actually 2 statements. When this runs, the entire TEST2 table will now be Volunteer AND you have a resultset from the select statement.

    UPDATE [dbo].[TEST2]

    SET event_role = 'Other'

    SELECT ID

    FROM dbo.TEST1

    WHERE ? = >2

    This is pretty much the same thing as above.

    ------try subquery but does not work because of multipe values errors

    UPDATE [dbo].[TEST2]t

    SET event_role = 'Speaker'

    WHERE t.id IN (SELECT ID

    FROM dbo.TEST1

    WHERE IsVolunteer = -1)

    AND event_role IS NULL

    I am not really sure what you are trying to do here.

    You did a great job posting ddl for TEST1. Unfortunately a lot of your code is using values from other tables and we don't have those. If you can try to explain more clearly what you are trying to do we can probably help.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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');

  • umm....this new problem is not any better than the last one. Your inserts don't work as posted, your sample data compared to your output doesn't seem to match. The description of what you are trying to do here is totally unclear. It seems that your tables are denormalized which is causing you some issues. Please post enough information so that we can understand the problem.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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');

  • Hi azdeji,

    I understand what you are trying to achieve, but you won't be able to do it with an update, since you need to create rows for each possibility, you will have to use unions, I think it would have been a bit better to do it more with a transactional type design, but to get it to do what you want it to do you can use the code below:

    SELECT Participant_ID, Supporter_ID, Event_Code, 'Speaker' AS EventRole, IsCoordinator, Speaker, IsVolunteer, IsInstructor, Attended_INT

    FROM table1 t

    WHERE Speaker = -1

    UNION

    SELECT Participant_ID, Supporter_ID, Event_Code, 'Attended_INT' AS EventRole, IsCoordinator, Speaker, IsVolunteer, IsInstructor, Attended_INT

    FROM table1 t

    WHERE Attended_INT = -1

    just keep with the unions (copy paste), each time changing the where clause to the next column you want to check for, it will give you the desired result, I know the solution is not dynamic for when you want new "is-something" roles, but the design is already not dynamic in the sense that you have to add a new column for each possibility already,

    I hope it answers your question,

    Kind regards

  • Thanks c_o, for helping out.

    the SQL code works perfectly!!

    Do you know how to create a hierarchy group from the event_roles?

    Thanks again.

  • Hi Azdeji,

    I am glad it worked,

    mmm, to do that you may have to change your schema, you will be able to make it work as is, but you are going to create a lot of trouble for yourself going forward, so it will be better to fix it now, but that means your schema will change quite a bit, will that be a problem? I will see if I get a chance today to quickly do it both ways, then you can choose if you want to continue with your current schema or if you would prefer to change it,

    😉

  • I would prefer to change the schema now. How would I go about doing that?

    Thank you!

  • Hi Azedji,

    The structure below I think has a bit more flexibility, you will see that you can add a role at any time, there may be better ways of doing the hierarchy select, but the structure will allow for the dynamic addition of new roles, new participants, and anything else you will need, the important table is the mapping table which allows you to say which participants were at which event, then your select handles the rest, you can copy the join for the event join and each time link it to the previous one, which will will take the hierarchy to the next level, for each parent. Like I said, the hierarchy may not be the ultimate solution, but at least your data will be in a much more dynamic structure? Comments welcome.

    I don't know if my explanation is good enough, you are welcome to shoot with specific questions on which data you will have to put where to make it work if the structure is not self-explanatory enough,

    CREATE TABLE [dbo].[Event]

    (

    [EventID] INT IDENTITY NOT NULL,

    [EventName] VARCHAR(150) NULL

    )

    CREATE TABLE [dbo].[EventRole]

    (

    [EventRoleID] INT IDENTITY NOT NULL,

    [EventRoleName] VARCHAR(150) NULL,

    [EventRoleParentID] INT NULL

    )

    CREATE TABLE [dbo].[ParticipantMap]

    (

    [EventID] INT NOT NULL,

    [ParticipantID] INT NOT NULL

    )

    CREATE TABLE [dbo].[Participants]

    (

    [ParticipantID] INT IDENTITY(1,1) NOT NULL,

    [ParticipantName] VARCHAR(50) NOT NULL,

    [EventRoleID] INT NOT NULL

    )

    INSERT INTO [dbo].[Event] VALUES ('Event1')

    INSERT INTO [dbo].[Event] VALUES ('Event2')

    INSERT INTO [dbo].[Event] VALUES ('Event3')

    INSERT INTO [dbo].[Event] VALUES ('Event4')

    INSERT INTO [dbo].[EventRole] VALUES ('Speaker',NULL)

    INSERT INTO [dbo].[EventRole] VALUES ('Attendent','1')

    INSERT INTO [dbo].[EventRole] VALUES ('Instructor',NULL)

    INSERT INTO [dbo].[EventRole] VALUES ('Coordinator','3')

    INSERT INTO [dbo].[Participants] VALUES ('Person1','1')

    INSERT INTO [dbo].[Participants] VALUES ('Person2','1')

    INSERT INTO [dbo].[Participants] VALUES ('Person3','2')

    INSERT INTO [dbo].[Participants] VALUES ('Person4','2')

    INSERT INTO [dbo].[Participants] VALUES ('Person5','3')

    INSERT INTO [dbo].[Participants] VALUES ('Person6','3')

    INSERT INTO [dbo].[Participants] VALUES ('Person7','4')

    INSERT INTO [dbo].[ParticipantMap] VALUES ('1','1')

    INSERT INTO [dbo].[ParticipantMap] VALUES ('1','3')

    INSERT INTO [dbo].[ParticipantMap] VALUES ('1','4')

    INSERT INTO [dbo].[ParticipantMap] VALUES ('1','5')

    INSERT INTO [dbo].[ParticipantMap] VALUES ('2','1')

    INSERT INTO [dbo].[ParticipantMap] VALUES ('2','2')

    INSERT INTO [dbo].[ParticipantMap] VALUES ('3','3')

    INSERT INTO [dbo].[ParticipantMap] VALUES ('3','4')

    INSERT INTO [dbo].[ParticipantMap] VALUES ('3','5')

    INSERT INTO [dbo].[ParticipantMap] VALUES ('3','6')

    SELECT

    EventName

    , ParticipantName

    , erL1.EventRoleName AS ParticipantRole

    , erL2.EventRoleName AS ParticipantParentRole

    FROM

    [dbo].[Event] e

    LEFT JOIN [dbo].[ParticipantMap] pm ON e.EventID = pm.EventID

    LEFT JOIN [dbo].[Participants] p ON pm.ParticipantID = p.ParticipantID

    LEFT JOIN [dbo].[EventRole] erL1 ON p.EventRoleID = erL1.EventRoleID

    LEFT JOIN [dbo].[EventRole] erL2 ON erL1.EventRoleParentID = erL2.EventRoleID

  • Thanks c_o! it works perfectly 😀

    just a a few simply questions relating to my actual table which contains 200000+ rows and additional columns I did not include in the sample table.

    If wanted to add more groups into EventRole, I would simply use the sql scrip below?

    CREATE TABLE [dbo].[Registered]

    (

    [ParticipantID] INT IDENTITY(1,1) NOT NULL,

    [RegisteredInfo] VARCHAR(50) NOT NULL,

    [EventRoleID] INT NOT NULL

    )

    INSERT INTO [dbo].[EventRole] VALUES ('Registered','4')

    Do I also need to add every participant level manually? I have 200000+ participant in the table.

    INSERT INTO [dbo].[Participants] VALUES ('Person1','1')

    INSERT INTO [dbo].[Participants] VALUES ('Person2','1')

    INSERT INTO [dbo].[Participants] VALUES ('Person3','2')

    INSERT INTO [dbo].[Participants] VALUES ('Person4','2')

    ParticipantMap table is simple matching the EventRole to the participant table but is this manually entry also?

    INSERT INTO [dbo].[ParticipantMap] VALUES ('1','1')

    INSERT INTO [dbo].[ParticipantMap] VALUES ('1','3')

    INSERT INTO [dbo].[ParticipantMap] VALUES ('1','4')

    Thanks for the help Again!

  • Ya, see, if you already have a data set in your original schema, it makes it a bit more difficult, as you will have to migrate your data to the new schema, if the setup was for an app which will be receiving data for the first time, then it would almost be easier, as you can set up the schema and have the app populate it in a specific format,

    To answer the first insert portion, no, the whole idea is to have all your event roles in one table, and to not have to create a table/column for each type, then assigning your new type to a participant,

    it basically works like this:

    Case 1

    --New participant, existing role

    ----Add participant with id of role to which they belong to in participants table

    Case 2

    --New participant, new role

    ----Add role to eventrole table, with the id of the parent role if it has one (say like student, would have id of teacher in parentid, and teacher would have principal's id in parentid, but principal would have null)

    ----add participant with id of new role created

    **This will ensure that you can always add new roles,without having to do any changes to your schema

    in your case, you don't have to re-capture all the data, but to migrate may be a bit difficult, depending on your current data structure, there were a few different versions you mentioned previously in your post, so by now I am not sure which one you have currently, but what you basically need to do is to populate the specific fields of the new schema table by doing a select from your current schema table to be in the format of the new schema table

    ex. say your current format is in the format where you have an event with different participants and then you have a column which identifies the role of the participant, (as I losely understand, this is how your current schema is), in this case you will do your insert into the new schema participant table as follows:

    --say you want to add all the participants who are speakers

    INSERT INTO Participants

    SELECT ParticipantName

    , 1

    FROM OriginalTable

    WHERE isSpeaker = -1

    then from there on, you can do that to "map" each of the participants to a role and thus build your participants table, you will also have to find similar ways of populating the Event and Participant map tables,

    I hope it makes sense, it makes it a bit difficult as currently I am not even sure if your original tables are in such a format so that you will be able to do extracts like these, but I am sure if you understand exactly how the new schema works, you will be able to do the mappings like this, and thus populate the tables from your original tables with only a little effort, please do not go and try to re-type everything, or something like that, there is always an easier way

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply