如何在Sql Server中获取格式化结果 [英] How Can I Get Formated Result In Sql Server

问题描述

嗨..

我的结果如下：

< tr>

州名	类别	NoOfRooms	NoOfHotels
ANDHRA PRADESH	5星级Delux	1832	8
ANDHRA PRADESH	5星	1902	13
ANDHRA PRADESH	4星	1116	9
ANDHRA PRADESH	3星	5038	78

我想要格式化结果





< br $>


< tr> < td> NoOfHotels

州名	5 Star Delux	5星	4星	3星
ANDHRA PRADESH
NoOfRooms	1832	1902	1116	5038
8	13	9	78

请帮助...

Hi..
I have results in columns like this :

State Name	Category	NoOfRooms	NoOfHotels
ANDHRA PRADESH	5 Star Delux	1832	8
ANDHRA PRADESH	5 Star	1902	13
ANDHRA PRADESH	4 Star	1116	9
ANDHRA PRADESH	3 Star	5038	78

I want result in format

State Name	5 Star Delux	5 Star	4 Star	3 Star
ANDHRA PRADESH
NoOfRooms	1832	1902	1116	5038
NoOfHotels	8	13	9	78

Pls help...

推荐答案

我创建了给你一个样品，

希望这会对你有帮助。

Hi,
I have created a sample for you,
Hope this will help you.

-- 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;

我想知道你是否可以'PIVOT'这些信息



http://technet.microsoft.com/en-us/library /ms177410(v=sql.105).aspx [ ^ ]

I'm wondering if you can 'PIVOT' the information

http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx[^]

这篇关于如何在Sql Server中获取格式化结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！