SQL Server - 将连接的行显示为列

I have two tables defined as the following:

我有两个表定义如下:

User (ID int, name varchar)
Hours (UserID int, [date] date, hours float)

Now i can perform a join to get the number of hours for each person like so:

现在我可以进行加入以获得每个人的小时数,如下所示:

   SELECT U.ID, U.Name, H.[date], H.Hours 
     FROM Users U 
LEFT JOIN Hours H ON U.ID = H.UserID
    WHERE H.Date > '2011-01-01' 
      AND H.Date < '2011-02-01'

Which would give me a result set with the following columns (and between the date range):

哪个会给我一个包含以下列的结果集(以及日期范围之间):

ID, Name, Date, Hours

What I would like to do is change the output columns so that it appears more like a spreadsheet:

我想要做的是更改输出列,使其看起来更像电子表格:

ID, Name, 2011-01-01, 2011-01-02, 2011-01-03 ..... 2011-01-31

and the corresponding columns with the correct hour values.

以及具有正确小时值的相应列。

Is this possible?

这可能吗?

1 个解决方案

#1

Well, you are gonna need to use dynamic sql for that, if you want to change the date ranges. So first, take a look to this link. Then you can try the following:

好吧,如果你想改变日期范围,你需要使用动态sql。首先,看看这个链接。然后你可以尝试以下方法:

DECLARE @Dates NVARCHAR(MAX), @Query NVARCHAR(MAX)
SET @Dates = ''

SELECT @Dates = @Dates + '[' + CONVERT(VARCHAR(10),[date],120) +'],'
FROM Hours
WHERE [Date] >= @StartDate AND [Date] < @EndDate
GROUP BY CONVERT(VARCHAR(10),[date],120)
ORDER BY  CONVERT(VARCHAR(10),[date],120)

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

SET @Query = '
SELECT ProviderName, '+@Dates+'
FROM (  SELECT U.ID, U.Name, H.[date], H.Hours 
        FROM Users U 
        LEFT JOIN Hours H 
        ON U.ID = H.UserID
        WHERE H.Date >= '''+CONVERT(VARCHAR(8),@StartDate,112)+''' 
        AND H.Date < '''+CONVERT(VARCHAR(8),@EndDate,112)+''') T
PIVOT(SUM(Hours) FOR [date] IN ('+@Date+')) AS PT'

EXEC sp_executesql @Query