Hi,
I have created a sample for you,
Hope this will help you.
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 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 )
DECLARE @MyColumns1 AS NVARCHAR(MAX),
@SQLquery1 AS NVARCHAR(MAX)
select @MyColumns1 = STUFF((SELECT ',' + QUOTENAME(Categorys)
FROM Hotels
GROUP BY Categorys
ORDER BY Categorys
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
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;