在 SQL Server 中使用数据透视表将日期显示为星期几

发布于 2024-09-15 13:08:54 字数 792 浏览 4 评论 0原文

我有一个包含 DateTime 类型列的表， 我想在日期时间列上旋转，以便日期显示为列（如周一、周二等）。

例如，我有一个像这样的表（不记得 SQL Server 如何显示完整的日期时间，但你明白了）：

BugNo | DateOpened | TimeSpent

1234  | 16/08/2010 | 1.0 
4321  | 16/08/2010 | 3.5 
9876  | 17/08/2010 | 1.5 
6789  | 18/08/2010 | 7.0 
6789  | 19/08/2010 | 6.5 
6789  | 20/08/2010 | 2.5

我想以 DateOpened 列为中心来创建一个像这样的结果集

|TimeSpentOnBugByDay|  Mon  |  Tue  | Wed | Thu  | Fri | Sat | Sun
1234                    1
4321                    3.5
9876                           1.5
6789                                   7.0
6789                                         6.5
6789                                                2.5

我应该指出，我一次只会检索一周。

我不确定这是否可能，尽管我很确定我以前见过类似的东西（我没有写）。

原文

I have table that has a column of type DateTime, I would like to pivot on the datetime column so that the dates are displayed as columns (as days of the week Monday, Tuesday etc).

So for example I have a table like this (can't remember how SQL Server displays full datetimes but you get the idea):

BugNo | DateOpened | TimeSpent

1234  | 16/08/2010 | 1.0 
4321  | 16/08/2010 | 3.5 
9876  | 17/08/2010 | 1.5 
6789  | 18/08/2010 | 7.0 
6789  | 19/08/2010 | 6.5 
6789  | 20/08/2010 | 2.5

I would like to pivot on the DateOpened column to create a result set like this

|TimeSpentOnBugByDay|  Mon  |  Tue  | Wed | Thu  | Fri | Sat | Sun
1234                    1
4321                    3.5
9876                           1.5
6789                                   7.0
6789                                         6.5
6789                                                2.5

I should point out that I'll only be retrieving one week at a time.

I'm not sure if this is possible, though I'm pretty certain I have seen something like this before (that I didn't write).

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

极致的悲 2024-09-22 13:08:54

你可以这样做

SELECT BugNo, [Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday]
FROM (
    SELECT BugNo, DATENAME(dw, DateOpened) AS DayWeek, TimeSpent
    FROM Bugs
    ) AS src
    pivot (
        SUM(TimeSpent) FOR DayWeek IN ([Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday])
    ) AS pvt

You can do this

SELECT BugNo, [Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday]
FROM (
    SELECT BugNo, DATENAME(dw, DateOpened) AS DayWeek, TimeSpent
    FROM Bugs
    ) AS src
    pivot (
        SUM(TimeSpent) FOR DayWeek IN ([Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday])
    ) AS pvt

回复收藏 0 原文

~没有更多了~