Create Hierarchy Group from one table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Create Hierarchy Group from one table?

Author

Topic

azdeji
Starting Member

3 Posts

Posted - 2013-05-08 : 14:29:49

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

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-05-08 : 14:39:32

What have you tried so far?
You can use alias to reference a table multiple times.

djj

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-08 : 14:42:50

Can you explain the scenario as what you have and what are you trying to acheive? Do you want to create a structure to store Hierarchy/ParentHierarchy group sort of relation?? or what?

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources