Creating a hierarchy group from one table column containing both parent and child hierarchy info

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

  • 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/

  • 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.

  • 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/

  • 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

  • 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

    ?

  • 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