sql - SQL 报告 - 按星期几透视数据？

Question

我正在写一份报告，需要显示一周的收据，按地点分组，一周中的每一天都有一个单独的列。这将通过 SSRS 和 SQL Server 2008 R2 提供。最终结果应如下所示:(但对于一周中的所有 7 天)

Location    Monday    Tuesday
Building3   $100      $75
Building4   $25       $35
Building5   $105      $21

我编写了以下 T-SQL SELECT 语句来从我的报告表变量 (@reporting) 中获取这些数据。然后按位置对输出进行分组以获得上面显示的布局。

原始数据在一行中列出了收货日期、总计和位置。我想旋转数据，以便星期几在最前面。必须有比您在下面看到的更好的方法来做到这一点。有什么帮助吗？

    SELECT
        LocationKey.Location
        ,Sunday.Cost as Sunday
        ,Monday.Cost as Monday
    FROM 

        (
            SELECT DISTINCT Location 
            FROM @reporting
            WHERE Location NOT IN ('Building01', 'Building02', '')
        ) AS LocationKey

    LEFT JOIN  

(SELECT  

sunday.Location
,sunday.Cost

FROM @reporting as sunday (nolock) 

WHERE DATEPART(weekday,sunday.ReceiptDate)= 1

        ) AS Sunday
        ON Sunday.Location = LocationKey.Location

LEFT JOIN  

(SELECT  
Monday.Location,
Monday.Cost

FROM @reporting as Monday (nolock) 

WHERE DATEPART(weekday,Monday.ReceiptDate)= 2

        ) AS Monday
        ON Monday.Location = LocationKey.Location

Answer 1

可能最简单的方法是 PIVOT在 DATENAME .

SELECT location, 
       [Saturday], 
       [Sunday], 
       [Monday], 
       [Tuesday], 
       [Wednesday], 
       [Thursday], 
       [Friday] 
FROM   (SELECT COST, 
               location, 
               Datename(weekday, receiptdate) DAY 
        FROM   @reporting 
        WHERE  location NOT IN ( 'Building01', 'Building02', '' )) p 
      PIVOT ( 
        SUM (COST) 
        FOR DAY IN ( [Saturday], [Sunday], [Monday], [Tuesday], [Wednesday], 
       [Thursday], [Friday]) ) pvt 

看到它在这个 data.se query 上工作
另一种方法是使用多个自连接但不执行子查询。这里的关键是 Join 子句

SELECT LocationKey.Location,
       SUM(Sunday.Cost) As [Sunday],
       SUM(Monday.Cost) As [Monday],
       SUM(Tuesday.Cost) As [Tuesday],
       SUM(Wednesday.Cost) As [Wednesday],
       SUM(Thursday.Cost) As [Thursday],
       SUM(Friday.Cost) As [Friday],
       SUM(Saturday.Cost) As [Saturday]
FROM
      (SELECT DISTINCT Location 
            FROM @reporting
            WHERE Location NOT IN ('Building01', 'Building02', '')) LocationKey
      LEFT JOIN @Reporting Sunday 
      ON LocationKey.Location = Sunday.Location
         AND DATEPART(weekday,sunday.ReceiptDate)= 1
      LEFT JOIN @Reporting Monday
      ON LocationKey.Location = Monday.Location
         AND DATEPART(weekday,Monday.ReceiptDate)= 2
      LEFT JOIN @Reporting Tuesday
      ON LocationKey.Location = Tuesday.Location
         AND DATEPART(weekday,Tuesday.ReceiptDate)= 3
      LEFT JOIN @Reporting Wednesday
      ON LocationKey.Location = Wednesday.Location
         AND DATEPART(weekday,Wednesday.ReceiptDate)= 4
      LEFT JOIN @Reporting Thursday
      ON LocationKey.Location = Thursday.Location
         AND DATEPART(weekday,Thursday.ReceiptDate)= 5
      LEFT JOIN @Reporting Friday
      ON LocationKey.Location = Friday.Location
         AND DATEPART(weekday,Friday.ReceiptDate)= 6         
      LEFT JOIN @Reporting Saturday
      ON LocationKey.Location = Saturday.Location
         AND DATEPART(weekday,Saturday.ReceiptDate)= 7

您应该注意，您应该调用 SET DATEFIRST 或使用 @@DATEFIRST 的偏移量当您使用 DATEPART(weekday.. 时，保护您的查询免受默认设置更改和破坏您的查询的潜在影响