Hi
I need help creating a hierarchy group 'using' the data in Event_Role column in my sample table to create a grouping structure like below –
The 6 parent hierarchies which are 'Speaker', 'Coordinator', 'Volunteer', 'Instructor', 'Attendee', 'Registration'
And the child hierarchies (or lower levels) are Did Not Attend/Yes Attend for Attendee and Do Not Register/No Reg/Yes Reg for Registration.
I have 100000+ rows to create these hierarchy groups for and I have more groups and child hierarchies.
hierarchy Groups
Speaker
Coordinator
Volunteer
Instructor
Attendee--- child(Lv2)-- Did Not Attend/Yes Attend
Registration--child(Lv2)---Do Not Register/No Reg/Yes Reg
I have create the sample data in SQL below --
Thank you in Advance!!
Create table table1(
Participant_ID int not null
,Supporter_ID int not null
,Event_Code varchar (50)
,Event_Role varchar (100) null
)
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234221','23536','LEG_SWI_1995','Did Not Attend');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234221','23536','LEG_SWI_1995','Yes Reg');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234220','17238','LEG_SWI_1995','Did Not Attend');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234220','17238','LEG_SWI_1995','Yes Reg');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234219','23532','LEG_HAT_1996','Did Not Attend');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234219','23532','LEG_HAT_1996','Yes Reg');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234218','58195','LEG_HAT_1996','Did Not Attend');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234218','58195','LEG_HAT_1996','Yes Reg');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234217','23494','LEG_SWI_1995','Did Not Attend');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234217','23494','LEG_SWI_1995','Yes Reg');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234208','00442','LEG_OXF_1998','Yes Attended');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234182','65306','SWCM01','IsCoordinator');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234182','65306','SWCM01','No Reg');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234152','22969','LEG_HAT_1996','IsCoordinator');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234152','22969','LEG_HAT_1996','No Reg');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234146','32672','LEG_MAR_01','IsCoordinator');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234146','32672','LEG_MAR_01','No Reg');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234123','47377','LEG_OXF_1998','Yes Attended');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234122','38726','LEG_OXF_1998','Yes Attended');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234121','50341','LEG_BUR_0000','Yes Attended');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218893','90409','SEMF091212','Speaker');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218890','23749','RFSEAH091212','Speaker');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218889','90387','SEAJ251112','Speaker');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218884','65306','SERB031212','Speaker');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218874','58902','SECW021212','Speaker');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218231','50766','LM1301','Do Not Register');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218221','73854','SEGF050912','Speaker');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218218','28146','LM1301','Do Not Register');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218211','95575','SEDB220712','Speaker');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('217647','22632','GNR0001','Do Not Register');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('142470','08862','ELON09','IsVolunteer');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('142469','01362','ELON09','IsVolunteer');
INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('132899','92898','ELON09','IsVolunteer');
May 8, 2013 at 1:00 pm
Great job posting ddl and sample data. What do you want as output?
_______________________________________________________________
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/
May 8, 2013 at 2:04 pm
Wanted results something like below -
INSERT INTO Wanted results (Participant_ID,supporter_id,event_code,Event_role,Lv2 child,Lv3 child) VALUES ('234221','98130','BALL05','NULL','Do not register','Did Not Attend');
INSERT INTO Wanted results (Participant_ID,supporter_id,event_code,Event_role,Lv2 child,Lv3 child) VALUES ('234222','98130','BCC07','NULL','No Reg','Did Not Attend');
INSERT INTO Wanted results (Participant_ID,supporter_id,event_code,Event_role,Lv2 child,Lv3 child) VALUES ('234223','98130','CC2009','Volunteer','Yes Reg','Yes Attend');
INSERT INTO Wanted results (Participant_ID,supporter_id,event_code,Event_role,Lv2 child,Lv3 child) VALUES ('234224','11980','BALL05','NULL','Do not register','Did Not Attend');
INSERT INTO Wanted results (Participant_ID,supporter_id,event_code,Event_role,Lv2 child,Lv3 child) VALUES ('234225','11980','BCC07','NULL','No Reg','Did Not Attend');
INSERT INTO Wanted results (Participant_ID,supporter_id,event_code,Event_role,Lv2 child,Lv3 child) VALUES ('234226','11980','CC2009','Speaker','Yes Reg','Yes Attend');
Let me know if you have any other questions, thanks.
May 8, 2013 at 2:17 pm
azdeji (5/8/2013)
Wanted results something like below -
INSERT INTO Wanted results (Participant_ID,supporter_id,event_code,Event_role,Lv2 child,Lv3 child) VALUES ('234221','98130','BALL05','NULL','Do not register','Did Not Attend');INSERT INTO Wanted results (Participant_ID,supporter_id,event_code,Event_role,Lv2 child,Lv3 child) VALUES ('234222','98130','BCC07','NULL','No Reg','Did Not Attend');
INSERT INTO Wanted results (Participant_ID,supporter_id,event_code,Event_role,Lv2 child,Lv3 child) VALUES ('234223','98130','CC2009','Volunteer','Yes Reg','Yes Attend');
INSERT INTO Wanted results (Participant_ID,supporter_id,event_code,Event_role,Lv2 child,Lv3 child) VALUES ('234224','11980','BALL05','NULL','Do not register','Did Not Attend');
INSERT INTO Wanted results (Participant_ID,supporter_id,event_code,Event_role,Lv2 child,Lv3 child) VALUES ('234225','11980','BCC07','NULL','No Reg','Did Not Attend');
INSERT INTO Wanted results (Participant_ID,supporter_id,event_code,Event_role,Lv2 child,Lv3 child) VALUES ('234226','11980','CC2009','Speaker','Yes Reg','Yes Attend');
Let me know if you have any other questions, thanks.
Can you explain the logic here a little bit? I can't even come close to comprehending what the rules are here.
The sample data and the expected output don't seem to match. You have supporterID of 98130 and 11980 in your output but those values don't exist in the original data.
Can you explain what the levels are and how they are determined?
_______________________________________________________________
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/
May 8, 2013 at 2:37 pm
The Event_Role column contains both the parent and the child hierarchies which are -
Parent (level 1)
Participant_ID
Supporter_id
Event_code
Event_role
Child (level 2)
Do not register
No Reg
Yes Reg
Child Level3
Did Not Attend
Yes Attend
This possible because the Supporter_ID contains rows duplicates when the event column has a child/level2 or Level3 data in Event_Role like below -
Participant_ID Supporter_ID Event_Code Event_Role
234146 1832672 LEG_MAR_2013 IsCoordinator
234146 1832672 LEG_MAR_2013 No Reg
May 8, 2013 at 2:58 pm
azdeji (5/8/2013)
The Event_Role column contains both the parent and the child hierarchies which are -Parent (level 1)
Participant_ID
Supporter_id
Event_code
Event_role
Child (level 2)
Do not register
No Reg
Yes Reg
Child Level3
Did Not Attend
Yes Attend
This possible because the Supporter_ID contains rows duplicates when the event column has a child/level2 or Level3 data in Event_Role like below -
Participant_ID Supporter_ID Event_Code Event_Role
234146 1832672 LEG_MAR_2013 IsCoordinator
234146 1832672 LEG_MAR_2013 No Reg
I am trying to get a handle on what you want but you keep posting example output for data that doesn't exist in what you posted. You really are going to have to explain what it is you want here. I am certain I can help you with this but if I don't understand what you are trying to do I can't offer much 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/
Hi Azdeji,
To get the results in the format you want with each level being in a different column, your best bet would be to join the table multiple times, but this depends on if you mean hierarchy as in I am a teacher, my boss is the principal, and students are under me, therefore, the principal will be the parent, and the students will be the children objects, then you will have:
(where * represents blank space)
******|************|--Student
******|-------Teacher|--Student
******|************|--Student
******|
******|************|--Student
Principal-------Teacher|--Student
******|************|--Student
******|
******|************|--Student
******|-------Teacher|--Student
******|************|--Student
so if your data is in the format
EventRoleID EventRoleName ParentEventRoleID
1 Principal NULL
2 Teacher 1
3 Student 2
then you can do your hierarchy like this:
SELECT
ParticipantID
, SupporterID
, Level1.EventRole AS Level1
, Level2.EventRole AS Level2
, Level3.EventRole AS Level3
FROM Events e
LEFT JOIN EventRoles Level1
ON e.EventRoleID = Level1.EventRoleID
LEFT JOIN EventRoles Level2
ON Level1.ParentEventRoleID = Level2.EventRoleID
LEFT JOIN EventRoles Level3
ON Level2.ParentEventRoleID = Level3.EventRoleID
?
May 12, 2013 at 2:01 pm
Thank you! That's perfect
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply