Data to the post Retrieving Hierarchy
CREATE TABLE DEPARTMENT (Id int , Name varchar(80), ParentId int) GO INSERT DEPARTMENT (Id,Name,ParentId) Values (1, 'City Manager''s Office', NULL ) , (2, 'Community & Emergency Services', NULL ) , (3, 'Corporate Services', NULL ) , (4, 'Planning & Economic Development', NULL ) , (5, 'Public Health Services', NULL ) , (6, 'Public Works', NULL ) , (7, 'Executive Director, Human Resouces', 1 ) , (8, 'Director Strategic Partnerships & Revenue Generation', 1 ) , (9, 'Director Ontario Works', 2 ) , (10, 'Director Housing Services', 2 ) , (11, 'Director Children''s & Home Management Services', 2 ) , (12, 'Director Neighbourhood & Community Initiatives', 2 ) , (13, 'Division Director Recreation', 2 ) , (14, 'Senior Administrator Lodges', 2 ) , (15, 'Chief, Hamilton Fire Department', 2 ) , (16, 'Chief, Hamilton Paramedic Services', 2 ) , (17, 'Director Customer Service, POA', 3 ) , (18, 'Director Financial Planning, Administration & Policy', 3 ) , (19, 'Director Financial Services, Taxation & Corporate Controller', 3 ) , (20, 'Director Information Technology', 3 ) , (21, 'City Clerk', 3 ) , (22, 'City Solicitor', 3 ) , (23, 'Executive Leadership Associate', 4 ) , (24, 'Director Open for Business Initiatives', 4 ) , (25, 'Director Building & Chief Building Official', 4 ) , (26, 'Director Economic Development', 4 ) , (27, 'Director, Planning & Chief Planner', 4 ) , (28, 'Director Tourism & Culture', 4 ) , (29, 'Director Licensing and By-law Services', 4 ) , (30, 'Senior Director Growth Management', 4 ) , (31, 'Director LRT', 4 ) , (32, 'AMOH/Director Communicable Disease Control & Wellness', 5 ) , (33, 'Director Healthy Environments', 5 ) , (34, 'Director Healthy Families', 5 ) , (35, 'Director Planning & Business Improvement', 5 ) , (36, 'Director Energy, Fleet & Facilities', 6 ) , (37, 'Director Engineering Services', 6 ) , (38, 'Director Environmental Services', 6 ) , (39, 'Acting Director Hamilton Water', 6 ) , (40, 'Director Operations', 6 ) , (41, 'Director Transit', 6 ) , (42, 'Director/Associate General Manager Transportation', 6 ) , (43, 'Access & Equity, Human Rights', 7 ) , (44, 'HR Systems & Operations', 7 ) , (45, 'Employee & Labour Relations', 7 ) , (46, 'Talent Management', 7 ) , (47, 'Health, Safety & Wellness', 7 ) , (48, 'Organizational Development', 7 ) , (49, 'Hamilton Farmers'' Market', 8 ) , (50, 'Revenue Generation', 8 ) , (51, 'Land Development', 8 ) , (52, 'City Enrichment Fund', 8 ) , (53, 'Eligibility Review', 9 ) , (54, 'Family Support', 9 ) , (55, 'Business & Systems Support', 9 ) , (56, 'Addiction Services', 9 ) , (57, 'Eligibility Verification Process', 9 ) , (58, 'Employment Services', 9 ) , (59, 'Helping Hands', 9 ) , (60, 'Ontario Works', 9 ) , (61, 'Homelessness Policy & Programs', 10 ) , (62, 'Domiciliary Hostel & Emergency Shelter', 10 ) , (63, 'Investment/Affordable Housing', 10 ) , (64, 'Social Housing', 10 ) , (65, 'Child Care Systems', 11 ) , (66, 'Early Years Systems', 11 ) , (67, 'Red Hill Family Centre', 11 ) , (68, 'Home Management', 11 ) , (69, 'Business Support', 11 ) , (70, 'Department Planning', 11 ) , (71, 'Citizen Engagement', 12 ) , (72, 'Communications', 12 ) , (73, 'Immigration', 12 ) , (74, 'Neighbourhood Development', 12 ) , (75, 'Age Friendly Hamilton', 12 ) , (76, 'Urban Indigenous Strategy', 12 ) , (77, 'Arena Operations', 13 ) , (78, 'Aquatics', 13 ) , (79, 'Business Support', 13 ) , (80, 'Program Development', 13 ) , (81, 'Recreation Centres', 13 ) , (82, 'Seniors', 13 ) , (83, 'Sports Policy & Allocation', 13 ) , (84, 'Macassa Lodge', 14 ) , (85, 'Wentworth Lodge', 14 ) , (86, 'Corporate Radio System', 15 ) , (87, 'Emergency Management', 15 ) , (88, 'Fire Services: Emergency Response, Fire Dispatch, Training, Mechanical & Stores', 15 ) , (89, 'Fire Prevention, Public Education & Community Safety & Planning', 15 ) , (90, 'Community Neighbourhood Public Education & Programs', 16 ) , (91, 'Emergency Medical Response & Patient Transportation', 16 ) , (92, 'Logistics & Planning', 16 ) , (93, 'Professional Standards & Continuous Quality Improvement', 16 ) , (94, 'Customer Contact Centre', 17 ) , (95, 'Municipal Service Centres', 17 ) , (96, 'Service Channel', 17 ) , (97, 'Channel Monitoring & Reporting', 17 ) , (98, 'Provincial Offences Administration Office', 17 ) , (99, 'Capital Budgets & Development', 18 ) , (100, 'Current Budgets & Fiscal Planning', 18 ) , (101, 'Investments', 18 ) , (102, 'Finance & Administration', 18 ) , (103, 'Accounting', 19 ) , (104, 'Accounts Payable & Receivable', 19 ) , (105, 'Business Applications', 19 ) , (106, 'Payroll & Pensions', 19 ) , (107, 'Procurement', 19 ) , (108, 'Tax Appeals', 19 ) , (109, 'Tax Billings & Collections', 19 ) , (110, 'Tax Customer Service', 19 ) , (111, 'Business Applications', 20 ) , (112, 'Infrastructure & Operations', 20 ) , (113, 'Security & Technology', 20 ) , (114, 'Elections/Print & Mail', 21 ) , (115, 'Legislative Services', 21 ) , (116, 'Records/Freedom of Information', 21 ) , (117, 'Legal Services', 22 ) , (118, 'Risk Management', 22 ) , (119, 'Parking Operations & Maintenance', 23 ) , (120, 'Parking Enforcement & School Safety', 23 ) , (121, 'P&ED Leadership Initiatives', 23 ) , (122, 'AMANDA Technology & Services', 24 ) , (123, 'Open for Business Initiatives', 24 ) , (124, 'Building Engineering & Zoning', 25 ) , (125, 'Building Inspections', 25 ) , (126, 'Plan Examination', 25 ) , (127, 'Business Development', 26 ) , (128, 'Business Incubator', 26 ) , (129, 'Real Estate', 26 ) , (130, 'Small Business', 26 ) , (131, 'Urban Renewal', 26 ) , (132, 'Community Planning & GIS Services', 27 ) , (133, 'Development Planning, Heritage & Design', 27 ) , (134, 'Policy Planning & Zoning By-law Reform', 27 ) , (135, 'Events', 28 ) , (136, 'Cultural Development', 28 ) , (137, 'Heritage Resource Management', 28 ) , (138, 'Public Art & Projects', 28 ) , (139, 'Tourism & Creative Industries', 28 ) , (140, 'Animal Services', 29 ) , (141, 'Licensing', 29 ) , (142, 'Municipal Law Enforcement', 29 ) , (143, 'Construction', 30 ) , (144, 'Development Engineering', 30 ) , (145, 'Engineering Approvals', 30 ) , (146, 'Growth Planning', 30 ) , (147, 'Infrastructure Planning', 30 ) , (148, 'Communications and Engagement', 31 ) , (149, 'Technical Coordination Team', 31 ) , (150, 'AMOHs', 32 ) , (151, 'Infectious Diseases', 32 ) , (152, 'Vaccine Program', 32 ) , (153, 'ADGS/MHSO', 32 ) , (154, 'Mental Health & Harm Reduction', 32 ) , (155, 'Tobacco Control & Prevention', 33 ) , (156, 'Vector Borne Disease', 33 ) , (157, 'Safe Water', 33 ) , (158, 'Health Hazards', 33 ) , (159, 'Food Safety', 33 ) , (160, 'Chronic Disease Prevention, Cancer Prevention, Activity, Friendly Communities', 33 ) , (161, 'Chronic Disease Prevention, Healthy Food Systems, Workplace Health', 33 ) , (162, 'Child & Adolescent Services', 34 ) , (163, 'Child Health, Nutrition', 34 ) , (164, 'Child Health, Parenting & Child Safety', 34 ) , (165, 'Community Dental Services', 34 ) , (166, 'Elementary School Program', 34 ) , (167, 'Healthy Babies Healthy Children Team 1 & 2', 34 ) , (168, 'Reproductive Health/Early ID', 34 ) , (169, 'Secondary School Program', 34 ) , (170, 'Business Operations', 35 ) , (171, 'Epidemiology & Evaluation', 35 ) , (172, 'Strategy & Health Equity', 35 ) , (173, 'Central Fleet', 36 ) , (174, 'Energy Initiatives', 36 ) , (175, 'Facility Management & Capital Programs', 36 ) , (176, 'Facilities Operations & Technical Services', 36 ) , (177, 'Strategic Planning & Capital Compliance', 36 ) , (178, 'Facilities Planning & Business Support', 36 ) , (179, 'Golf & Stadium Operations', 36 ) , (180, 'Asset Management', 37 ) , (181, 'Construction', 37 ) , (182, 'Design', 37 ) , (183, 'Geomatics & Corridor Management', 37 ) , (184, 'Waterfront Development', 37 ) , (185, 'Forestry & Horticulture', 38 ) , (186, 'Parks & Cemeteries', 38 ) , (187, 'Waste Collection', 38 ) , (188, 'Recycling & Waste Disposal', 38 ) , (189, 'Landscape Architectural Services', 38 ) , (190, 'Compliance & Regulations', 39 ) , (191, 'Woodward Upgrades', 39 ) , (192, 'Water & Wastewater Operations', 39 ) , (193, 'Water & Wastewater Planning & Capital', 39 ) , (194, 'Business Programs', 40 ) , (195, 'Capital Rehabilitation & Technical Operations', 40 ) , (196, 'Roads & Maintenance', 40 ) , (197, 'Accessible Transportation Services', 41 ) , (198, 'Transit Fleet Maintenance', 41 ) , (199, 'Transit Operations', 41 ) , (200, 'Transit Planning & Customer Service', 41 ) , (201, 'Transit Strategy & Infrastructure', 41 ) , (202, 'Transit Support Services', 41 ) , (203, 'Transportation Planning Services', 42 ) , (204, 'Traffic Operations & Engineering', 42 ) , (205, 'Policy & Programs', 42 )
