将这些行转换为列 [英] Convert these rows to columns

问题描述

我有一个相当复杂的查询，我想知道如何将这些从行转换为列，以便它们以下列格式显示.平均值是应该使用的列，总计只是用于信息但不需要汇总.

I have a fairly complicated query, and I'm wondering how I can convert these from rows to columns, so they display in the following format. Average is the column that should be used, total is just there for information but doesn't need to be rolled up.

一些当前结果的片段:

SELECT dayofweek                    AS Day, 
       Format(onhour, '00') + ':00' AS Hour, 
       Sum(totals)                  AS Total, 
       Avg(totals)                  AS Average 
FROM   (SELECT Datename(weekday, D.fordate) AS DayOfWeek, 
               H.onhour, 
               Isnull(T.totals, 0)          AS Totals 
        FROM   (SELECT DISTINCT T.fordate 
                FROM   #visit AS T) AS D 
               CROSS apply (VALUES(0), 
                                  (1), 
                                  (2), 
                                  (3), 
                                  (4), 
                                  (5), 
                                  (6), 
                                  (7), 
                                  (8), 
                                  (9), 
                                  (10), 
                                  (11), 
                                  (12), 
                                  (13), 
                                  (14), 
                                  (15), 
                                  (16), 
                                  (17), 
                                  (18), 
                                  (19), 
                                  (20), 
                                  (21), 
                                  (22), 
                                  (23)) AS H(onhour) 
               LEFT OUTER JOIN #visit AS T 
                            ON H.onhour = T.onhour 
                               AND D.fordate = T.fordate) a 
WHERE  onhour NOT IN ( 0, 1, 2, 3, 
                       4, 5, 6, 7, 
                       20, 21, 22, 23 ) 
GROUP  BY dayofweek, 
          onhour 
ORDER  BY CASE 
            WHEN dayofweek = 'Monday' THEN 1 
            WHEN dayofweek = 'Tuesday' THEN 2 
            WHEN dayofweek = 'Wednesday' THEN 3 
            WHEN dayofweek = 'Thursday' THEN 4 
            WHEN dayofweek = 'Friday' THEN 5 
            WHEN dayofweek = 'Saturday' THEN 6 
            WHEN dayofweek = 'Sunday' THEN 7 
          END ASC, 
          onhour 

推荐答案

您可以使用交叉表查询来获取结果.

You could use a Cross tabs query to get your result.

SELECT CONVERT(varchar(8), DATEADD(hh, onhour, 0), 108) + ' - ' 
       + CONVERT(varchar(8), DATEADD(hh, onhour+1, 0), 108) AS Hour, 
       Sum(totals)                  AS Total, 
       Avg(CASE WHEN DayOfWeek = 'Monday'    THEN totals END) AS Monday  , 
       Avg(CASE WHEN dayofweek = 'Tuesday'   THEN totals END) AS Tuesday , 
       Avg(CASE WHEN dayofweek = 'Wednesday' THEN totals END) AS Wednesday, 
       Avg(CASE WHEN dayofweek = 'Thursday'  THEN totals END) AS Thursday, 
       Avg(CASE WHEN dayofweek = 'Friday'    THEN totals END) AS Friday  , 
       Avg(CASE WHEN dayofweek = 'Saturday'  THEN totals END) AS Saturday, 
       Avg(CASE WHEN dayofweek = 'Sunday'    THEN totals END) AS Sunday
FROM   (SELECT Datename(weekday, D.fordate) AS DayOfWeek, 
               H.onhour, 
               Isnull(T.totals, 0)          AS Totals 
        FROM   (SELECT DISTINCT T.fordate 
                FROM   #visit AS T) AS D 
               CROSS apply (VALUES(0), 
                                  (1), 
                                  (2), 
                                  (3), 
                                  (4), 
                                  (5), 
                                  (6), 
                                  (7), 
                                  (8), 
                                  (9), 
                                  (10), 
                                  (11), 
                                  (12), 
                                  (13), 
                                  (14), 
                                  (15), 
                                  (16), 
                                  (17), 
                                  (18), 
                                  (19), 
                                  (20), 
                                  (21), 
                                  (22), 
                                  (23)) AS H(onhour) 
               LEFT OUTER JOIN #visit AS T 
                            ON H.onhour = T.onhour 
                               AND D.fordate = T.fordate) a 
WHERE  onhour NOT IN ( 0, 1, 2, 3, 
                       4, 5, 6, 7, 
                       20, 21, 22, 23 ) 
GROUP  BY onhour 
ORDER  BY onhour;

这篇关于将这些行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！