透视 SQL 查询 - 在列中显示日期 [英] Pivot SQL query - Show Date in column

问题描述

我当前的查询返回以下结果

my current query return the below result

我需要的是在列标题中显示日期.我知道必须使用 Pivot

What I need is I have to display date in column header. I know it have to be done using Pivot

示例:如果我们想在列标题中显示月份，那么查询将类似于..

Example: if we want to display month in column header then query will be something like..

SELECT  [January], [February], [March]
FROM    ( SELECT    [Month], SaleAmount
          FROM      Sales
        ) p PIVOT ( SUM(SaleAmount)
                    FOR [Month] 
                      IN ([January],[February],[March])
                  ) AS pvt

我认为我的结果查询应该看起来像 Pivot ((Price) FOR [DateVal] IN (What will be here?) ) 这里是结果列应该是什么样子

and I think my resultant query should be look like Pivot ( (Price) FOR [DateVal] IN (What will be here?) ) here is what resultant column should be like

注意:当前结果集中显示的是12月份的数据，但可以是任意月份.

Note: in the result set currently it show December month data, but it can be any month.

感谢您的帮助...

推荐答案

更新 4好的，既然您已经发布了所有查询并关注了您的评论，您应该试试这个:

Update 4 Ok, now that you posted all your query, and following your comments, you should try this:

DECLARE @FirstDay SMALLDATETIME
SELECT @FirstDay = CONVERT(SMALLDATETIME, (@year + '-' + @month + '-01')); 

WITH Dates AS  
(
  SELECT @FirstDay AS DateVal 
  UNION ALL 
  SELECT DATEADD(d, 1, DateVal) AS DateVal 
  FROM Dates 
  WHERE DATEADD(d, 1, DateVal) < DATEADD(m, 1, @FirstDay)
)

SELECT * INTO #Dates FROM Dates

DECLARE @hotelID INT, @packageID INT, @year VARCHAR(4), @Dates VARCHAR(1000), @month VARCHAR(2), @Query VARCHAR(MAX)
SELECT @hotelID=248, @packageID=76, @year='2010', @month='12',@Dates=''

SELECT co.*,wb.Name,rc.HotelName 
INTO #HotelData
FROM RCompetitorOccupancy co 
INNER JOIN websites wb ON wb.websiteid=co.websiteid
INNER JOIN RoomCompetitor rc ON rc.competitorid=co.competitorid
WHERE YEAR(occDate)=@year AND MONTH(occdate)=@month AND packageid=@packageID 
AND roomTypeid IN (SELECT roomtypeid FROM CompetitorRoomType WHERE DESCRIPTION=119)

SELECT @Dates = @Dates + '[' + CAST(DATEPART(DAY,DateVal) AS VARCHAR(2)) + '-' + LEFT(DATENAME(MONTH,DateVal),3)+ '],'
FROM #Dates
GROUP BY Dateval

SET @Dates = LEFT(@Dates,LEN(@Dates)-1)

SET @Query = '
SELECT roomtypeid, description, [Name], [HotelName], '+@Dates+'
FROM (  SELECT  HD.roomtypeid, HD.description, HD.[Name], HD.[HotelName], HD.Price, 
                CAST(DATEPART(DAY,DateVal) AS VARCHAR(2)) + ''-'' + LEFT(DATENAME(MONTH,DateVal),3) [Date]
        FROM #Dates D
        LEFT JOIN #HotelData HD
        ON D.DateVal = HD.OccDate) T
PIVOT ( SUM(Price) FOR [Date] IN ('+@Dates+') ) AS PT'

EXEC(@Query)

这篇关于透视 SQL 查询 - 在列中显示日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！