SQL Server Fun - I

Question

Hi All,

Lets have some SQL query fun.

This only for learning purpose, anyone can reply with sql queries that produce correct o/p as below.

I have a table named MenuMaster, with 12 rows.

 

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

I need to produce o/p as per attached image.



Query used for above o/p:

 

 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

Now your turn, write different query for above o/p. Dosent matter how the query is, just needed the o/p.