Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Updated muliple rows into one column? Why is it hard? HELP Expand / Collapse
Author
Message
Posted Tuesday, April 30, 2013 5:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 10:28 AM
Points: 67, Visits: 588
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

Post #1447956
Posted Tuesday, April 30, 2013 7:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 13,093, Visits: 12,574
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1448029
Posted Thursday, May 2, 2013 2:45 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 10:28 AM
Points: 67, Visits: 588
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_ID Supporter_ID Event_Code Event_Role IsCoordinator Speaker IsVolunteer IsInstructor Attended_INT Registered_INT
1894 0013 ER11 NULL 0 -1 0 0 -1 2
2893 9930 JR03 NULL 0 -1 0 0 -1 3
4704 4255 SR05 NULL -1 0 0 0 0 3
4611 2754 SR05 NULL -1 0 0 0 0 3
9009 8624 LL08 NULL -1 0 0 0 -1 2
2181 0766 0001 NULL -1 0 0 0 -1 2
4619 2636 SR05 NULL -1 0 0 0 0 3
1893 2010 CC11 NULL -1 0 0 0 0 2
4629 5285 SR05 NULL -1 0 0 0 0 3
2188 8902 1212 NULL 0 -1 0 0 0 3
9012 6569 LL08 NULL -1 0 0 0 -1 3
2902 3004 JR03 NULL 0 -1 0 0 -1 3
2879 5589 JR03 NULL 0 -1 0 0 -1 3
2188 0387 1112 NULL 0 -1 0 0 0 3
2188 0409 1212 NULL 0 -1 0 0 0 3
1424 8862 ON09 NULL 0 0 -1 0 0 2
4678 5510 SR05 NULL -1 0 0 0 -1 3
4682 4546 SR05 NULL -1 0 0 0 0 3
7665 3923 DC07 NULL -1 0 0 0 0 3
2182 5575 0712 NULL 0 -1 0 0 -1 3
4714 4913 SR05 NULL -1 0 0 0 0 3


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_ID Supporter_ID Event_Code Event_Role IsCoordinator Speaker IsVolunteer IsInstructor Attended_INT Registered_INT
1894 0013 ER11 Speaker 0 -1 0 0 -1 2
2893 9930 JR03 Speaker 0 -1 0 0 -1 3
4611 2754 SR05 Coordinator -1 0 0 0 0 3
9009 8624 LL08 Coordinator -1 0 0 0 -1 2
1893 2010 CC11 Do Not register 0 0 0 0 0 1
4629 5285 SR05 Coordinator -1 0 0 0 0 3
2188 8902 1212 Speaker 0 -1 0 0 0 3
9012 6569 LL08 Attended -1 0 0 0 -1 3
2902 3004 JR03 Speaker 0 -1 0 0 -1 3
2902 3004 JR03 Attended 0 -1 0 0 -1 3
2879 5589 JR03 Attended 0 -1 0 0 -1 3
2879 5589 JR03 Speaker 0 -1 0 0 -1 3
9012 6569 LL08 Coordinator -1 0 0 0 -1 3
2188 0387 1112 NO 0 0 0 0 0 2
1424 8862 ON09 Volunteer 0 0 -1 0 0 2
4678 5510 SR05 Coordinator -1 0 0 0 -1 3
4682 4546 SR05 Coordinator -1 0 0 0 0 3
7665 3923 DC07 Coordinator -1 0 0 0 0 3
2182 5575 0712 YES 0 0 0 0 -1 3



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

Post #1448729
Posted Thursday, May 2, 2013 7:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 13,093, Visits: 12,574
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1448806
Posted Friday, May 3, 2013 3:22 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 10:28 AM
Points: 67, Visits: 588
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');





Post #1449098
Posted Friday, May 3, 2013 4:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 11, 2014 6:05 AM
Points: 11, Visits: 54
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
Post #1449126
Posted Monday, May 6, 2013 5:44 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 10:28 AM
Points: 67, Visits: 588
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.
Post #1449939
Posted Monday, May 6, 2013 11:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 11, 2014 6:05 AM
Points: 11, Visits: 54
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,

Post #1449987
Posted Monday, May 6, 2013 11:57 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 10:28 AM
Points: 67, Visits: 588
I would prefer to change the schema now. How would I go about doing that?

Thank you!
Post #1449992
Posted Tuesday, May 7, 2013 6:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 11, 2014 6:05 AM
Points: 11, Visits: 54
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

Post #1450099
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse