将数据库行转换为列 [英] Convert Database Rows into Columns

问题描述

我需要将数据库行转换为列，并在Gridview中显示结果.我的数据库如下:

I need to convert Database rows into columns and show the result in Gridview. My DB is as follows:

ID  Hotel   cDate                   Price
-----------------------------------------------
1   Hotel1  12/22/2009 12:00:00 AM  15.0000
2   Hotel2  12/22/2009 12:00:00 AM  25.0000
3   Hotel3  12/22/2009 12:00:00 AM  60.0000
4   Hotel4  12/22/2009 12:00:00 AM  55.0000
.
.
.

我将显示以下结果:

cDate                    Hotel1 Hotel2 Hotel3 Hotel4
12/22/2009 12:00:00 PM    15     25     60     55
12/22/2009 12:00:00 AM    ..     ..     ..     ..
12/22/2009 12:00:00 AM   
12/22/2009 12:00:00 AM  

推荐答案

如果使用的是SQL Server 2005，则可以使用Pivot运算符.

If you're using SQL Server 2005 then you can use the Pivot operator.

请参阅此MSDN文章.

以下是SQL Server 2005 T-SQL要做的事情:

Here's the SQL Server 2005 T-SQL to do what you want to do:

DECLARE @listCol VARCHAR(2000)
DECLARE @query VARCHAR(4000)
SELECT  @listCol = STUFF(( SELECT DISTINCT
                                '],[' + Hotel
                        FROM    dbo.tblHotels
                        ORDER BY '],[' + Hotel
                        FOR XML PATH('')
                                    ), 1, 2, '') + ']'

SET @query =
'SELECT * FROM
      (SELECT cDate,Hotel,price
            FROM dbo.tblHotels) p
PIVOT (SUM(price) FOR Hotel
IN ('+@listCol+')) AS pvt'

EXECUTE (@query)

这篇关于将数据库行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！