SQL Server Fun - I

 

 CREATE TABLE [dbo].[MenuMaster](
 [MenuID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
 [ParentID] [numeric](18, 0) NOT NULL,
 [MenuName] [varchar](50) NULL,
 [MenuURL] [varchar](max) NULL
) ON [PRIMARY]
 
insert into MenuMaster values (0, 'Admins', NULL)
insert into MenuMaster values (1, 'UserMaster', 'UserMaster.aspx')
insert into MenuMaster values (1, 'RoleMaster', 'RoleMaster.aspx')
insert into MenuMaster values (1, 'VehicleMaster', 'VehicleMaster.aspx')
insert into MenuMaster values (0, 'DepartmentS', NULL)
insert into MenuMaster values (5, 'Maths', 'Maths.aspx')
insert into MenuMaster values (5, 'Physics', 'Physics.aspx')
insert into MenuMaster values (5, 'Biology', 'Biology.aspx')
insert into MenuMaster values (5, 'Commerce', 'Commerce.aspx')
insert into MenuMaster values (0, 'Reports', NULL)
insert into MenuMaster values (9, 'StudentsAttendence', 'StudentsAttendence.aspx')
insert into MenuMaster values (9, 'StudentsResults', 'StudentsResults.aspx')

 

 select a.MenuID, 
    CASE WHEN a.ParentID = 0 then a.MenuName else b.MenuName end 'MainMenu', 
    CASE WHEN a.ParentID = 0 then NULL else a.MenuName end 'SubMenu',
    a.MenuURL
 from MenuMaster a left join MenuMaster b
 on a.ParentID = b.MenuID