在SQL Server中使用数据透视显示日期作为星期几 [英] Using Pivot in SQL Server to Display dates as days of the week

问题描述

我的表格有一个类型为 DateTime的列，我想在datetime列上进行转换，以便日期显示为列（以星期为单位）星期一，星期二等）。

所以例如我有一个这样的表（不记得SQL Server如何显示完整的数据时间，但你得到的想法）： p>

  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 |星期一星期二周三|星期四周五星期六|太阳
 1234 1 
 4321 3.5 
 9876 1.5 
 6789 7.0 
 6789 6.5 
 6789 2.5 
  pre> 
 
 
我应该指出，我一次只能检索一个星期。
 
 
 
我不知道这是否可能，虽然我很确定我已经看到过这样的东西（我没有写）。
解决方案
你可以这样做
  SELECT BugNo，[Monday]，[Tuesday]，[Wednesday] ]，[星期五]，[星期六]，[星期日] 
 FROM（
 SELECT BugNo，DATENAME（dw，DateOpened）AS DayWeek，TimeSpent 
 FROM Bugs 
）AS src 
支点（
 SUM（TimeSpent）FOR DayWeek IN（[星期一]，[星期二]，[星期三]，[星期四]，[星期五]，[星期六]，[星期日]）
） AS pvt 
  
 
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).
解决方案
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


                        
这篇关于在SQL Server中使用数据透视显示日期作为星期几的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！