How Can I Get Formated Result In Sql Server

-- Create Table

CREATE TABLE [dbo].[Hotels](
	[State_Name] [varchar](30) NOT NULL,
	[Categorys] [varchar](30) NOT NULL,
	[NoOfRooms] Int NOT NULL,
	[NoOfHotels]  Int NOT NULL,
) ON [PRIMARY]
-- Insert Sample Data 
INSERT INTO [Hotels]
           ([State_Name] ,[Categorys],[NoOfRooms],[NoOfHotels])
     VALUES
           ('ANDHRA PRADESH', '5 Star Delux',  1832, 8 )
           
INSERT INTO [Hotels]
           ([State_Name] ,[Categorys],[NoOfRooms],[NoOfHotels])
     VALUES
           ('ANDHRA PRADESH',  '5 Star'  ,1902, 13  )
INSERT INTO [Hotels]
           ([State_Name] ,[Categorys],[NoOfRooms],[NoOfHotels])
     VALUES
           ('ANDHRA PRADESH',  '4 Star',  1116,  9   )
INSERT INTO [Hotels]
           ([State_Name] ,[Categorys],[NoOfRooms],[NoOfHotels])
     VALUES
           ('ANDHRA PRADESH',  '3 Star',  5038,  78 )


-- Using Pivot query to display your result .

DECLARE @MyColumns1 AS NVARCHAR(MAX),
    @SQLquery1  AS NVARCHAR(MAX)
-- here first we get all the ItemName which should be display in Columns we use this in our necxt pivot query
select @MyColumns1 = STUFF((SELECT ',' + QUOTENAME(Categorys) 
                    FROM Hotels
                    GROUP BY Categorys
                    ORDER BY Categorys
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
-- here we use the above all Item name to disoplay its price as column and row display
set @SQLquery1 = N'SELECT State_Name, ''No of Rooms'' as Type,' + @MyColumns1 + N' from 
             (
                 SELECT 
       State_Name, 
        NoOfRooms as TotRoom
     
        ,Categorys
    FROM Hotels
            ) x
            pivot 
            (
                 SUM(TotRoom)
                for Categorys in (' + @MyColumns1 + N')
            ) p 
            UNION  
            SELECT State_Name, ''No of Hotels''  as Type,' + @MyColumns1 + N' from 
             (
                 SELECT 
       State_Name, 
        NoOfHotels as TotRoom
     
        ,Categorys
    FROM Hotels
            ) x
            pivot 
            (
                 SUM(TotRoom)
                for Categorys in (' + @MyColumns1 + N')
            ) p 
            '
exec sp_executesql @SQLquery1;