如何在此表中使用交叉表

Question

我使用的是sql server 2008，我想对此表进行交叉标记。

Month   Affec   KPI     Total   KPI_%   Out     rep_in_10  ftm
Jan-11  30565   34623   42003   82.4    7380    7003       5024
Jan-12  20955   25915   27857   93      1942    4754       3518
Feb-11  27754   27757   36483   76.1    8726    5648       4189
Feb-12  19513   25188   26962   93.4    1774    5768       4185
Mar-11  22838   23758   29951   79.3    6193    4394       3282
Mar-12  18778   25098   26177   95.9    1079    5784       4105
Apr-11  20235   21950   25917   84.7    3967    3895       2967

至

            Jan-11  Jan-12  Feb-11  Feb-12  Mar-11  Apr-11
Affec       30565   
KPI         34623   
Total       42003   
KPI_%       82.4         
Out         7380    
rep_in_10   7003

Answer 1

对于这种类型的数据转换，您需要使用UNPIVOT函数，然后在SQL Server中应用PIVOT函数。

有两种方法可以实现这一点，一种是使用静态版本的动态sql对值进行硬编码，以将值生成为运行时。

静态版本：

其中的UNPIVOT片段从多个列中获取数据，并将其转换为两行。请注意，使用unpivot时要记住的一件事是，数据类型必须相同。因此，您可能需要对数据执行数据类型转换。

select [Month], value, col
from
(
  select DateName(month,[Month]) +'-'+Cast(datepart(year, [month]) as varchar(4)) Month,
    [Affec], [KPI], [Total], [KPI_%], [Out], [rep_in_10], [ftm]
  from yourtable
) src
unpivot
(
  value
  for col in ([Affec], [KPI], [Total], [KPI_%], [Out], [rep_in_10], [ftm])
) unpiv

请参阅SQL Fiddle with Demo

结果：

|         MONTH | VALUE |       COL |
-------------------------------------
|  January-2011 | 30565 |     Affec |
|  January-2011 | 34623 |       KPI |
|  January-2011 | 42003 |     Total |
|  January-2011 |  82.4 |     KPI_% |
|  January-2011 |  7380 |       Out |  ---etc

然后将PIVOT应用于月份：

select *
from
(
  select [Month], value, col
  from
  (
    select DateName(month,[Month]) +'-'+Cast(datepart(year, [month]) as varchar(4)) Month,
      [Affec], [KPI], [Total], [KPI_%], [Out], [rep_in_10], [ftm]
    from yourtable
  ) src
  unpivot
  (
    value
    for col in ([Affec], [KPI], [Total], [KPI_%], [Out], [rep_in_10], [ftm])
  ) unpiv
) src
pivot
(
  max(value)
  for month in ([January-2011], [February-2011], [March-2011],
             [April-2011], [January-2012], [February-2012], [March-2012])
) piv

请参阅SQL Fiddle with Demo

结果：

|       COL | JANUARY-2011 | FEBRUARY-2011 | MARCH-2011 | APRIL-2011 | JANUARY-2012 | FEBRUARY-2012 | MARCH-2012 |
------------------------------------------------------------------------------------------------------------------
|     Affec |        30565 |         27754 |      22838 |      20235 |        20955 |         19513 |      18778 |
|       ftm |         5024 |          4189 |       3282 |       2967 |         3518 |          4185 |       4105 |
|       KPI |        34623 |         27757 |      23758 |      21950 |        25915 |         25188 |      25098 |
|     KPI_% |         82.4 |          76.1 |       79.3 |       84.7 |           93 |          93.4 |       95.9 |
|       Out |         7380 |          8726 |       6193 |       3967 |         1942 |          1774 |       1079 |
| rep_in_10 |         7003 |          5648 |       4394 |       3895 |         4754 |          5768 |       5784 |
|     Total |        42003 |         36483 |      29951 |      25917 |        27857 |         26962 |      26177 |

动态版本：

上面的方法效果很好，如果你有一个已知数量的值，但是你的值是未知的，那么你就会想要使用动态sql。我猜你会想要一个动态版本，因为你会有一个未知的日期：

DECLARE @colsUnpivot AS NVARCHAR(MAX),
    @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @colsUnpivot = STUFF((SELECT DISTINCT ',' 
                      + quotename(c.name)
                    from sys.columns as C
                   where C.object_id = object_id('yourtable') and
                         C.name not in ('Month')
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @cols = STUFF((SELECT ',' + QUOTENAME(DateName(month,[Month]) +'-'+Cast(datepart(year, [month]) as varchar(4))) 
                    from yourtable
                    group by [Month]
                    order by [Month]
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT col,' + @cols + ' from 
             (
               select [Month], value, col
              from
              (
                select DateName(month,[Month]) +''-''+Cast(datepart(year, [month]) as varchar(4)) Month,
                  [Affec], [KPI], [Total], [KPI_%], [Out], [rep_in_10], [ftm]
                from yourtable
              ) src
              unpivot
              (
                value
                for col in ('+@colsunpivot+')
              ) unpiv
            ) x
            pivot
            (
              max(value)
              for [Month] in (' + @cols + ')
            ) p '

execute(@query)

请参阅SQL Fiddle with Demo

结果将与动态版本相同。

具有聚合的UNION ALL/CASE：

最后，如果您既不能访问UNPIVOT函数，也不能访问PIVOT函数，则可以使用UNION ALL取消透视，使用带有CASE的聚合函数透视数据：

select col,
  max(case when month='January-2011' then value end) [January-2011],
  max(case when month='February-2011' then value end) [February-2011],
  max(case when month='March-2011' then value end) [March-2011],
  max(case when month='April-2011' then value end) [April-2011],
  max(case when month='January-2012' then value end) [January-2012],
  max(case when month='February-2012' then value end) [February-2012],
  max(case when month='March-2012' then value end) [March-2012]
from
(
  select DateName(month,[Month]) +'-'+Cast(datepart(year, [month]) as varchar(4)) Month,
    [Affec] value,
    'Affec' col
  from yourtable
  union all
  select DateName(month,[Month]) +'-'+Cast(datepart(year, [month]) as varchar(4)) Month,
    [KPI] value,
    'KPI' col
  from yourtable
  union all
  select DateName(month,[Month]) +'-'+Cast(datepart(year, [month]) as varchar(4)) Month,
    [Total] value,
    'Total' col
  from yourtable
  union all
  select DateName(month,[Month]) +'-'+Cast(datepart(year, [month]) as varchar(4)) Month,
    [KPI_%] value,
    'KPI_%' col
  from yourtable
  union all
  select DateName(month,[Month]) +'-'+Cast(datepart(year, [month]) as varchar(4)) Month,
    [rep_in_10] value,
    'rep_in_10' col
  from yourtable
  union all
  select DateName(month,[Month]) +'-'+Cast(datepart(year, [month]) as varchar(4)) Month,
    [ftm] value,
    'ftm' col
  from yourtable
) src
group by col

请参阅SQL Fiddle with Demo

Answer 2

在我看来，你不应该这样做。你可以用PHP或你正在使用的任何东西在表示层很容易地做到这一点。数据库是用来获取数据的，而不是很好地格式化数据的。更多地将Mahmoud的回答作为概念的证明。但是查询永远不会像您现在用来获取数据的查询那样快。维护可能是反对它的另一个理由。

Answer 3

如下所示：

;WITH UNpivoted
AS
(
  SELECT MonthsValue, Value, month
  FROM (SELECT [Month], 
        CAST([Affec] AS VARCHAR(10)) Affec ,
        CAST([KPIprecent] AS VARCHAR(10)) KPIprecent,
        CAST([Total] AS VARCHAR(10)) Total, 
        CAST([KPI] AS VARCHAR(10)) KPI, 
        CAST([Out] AS VARCHAR(10)) [Out],
        CAST([rep_in_10] AS VARCHAR(10)) [rep_in_10], 
        CAST([ftm] AS VARCHAR(10)) ftm
        FROM table1
       ) t
  UNPIVOT
  (
    MonthsValue FOR Value IN([Affec], 
                             [KPIprecent], 
                             [Total], 
                             [KPI],        
                             [Out], 
                             [rep_in_10], 
                             [ftm])
  ) u
)
  SELECT
    value,
    [Jan-11], 
    [Jan-12], 
    [Feb-11], 
    [Feb-12],
    [Mar-11], 
    [Mar-12], 
    [Apr-11]
  FROM
  (
    SELECT monthsvalue, value, month
    FROM Unpivoted
  ) t
  PIVOT
  (MAX(monthsvalue) for Month IN ([Jan-11], 
                                 [Jan-12], 
                                 [Feb-11], 
                                 [Feb-12],
                                 [Mar-11], 
                                 [Mar-12], 
                                 [Apr-11])
  ) p;

SQL Fiddle Demo

这将为您提供：

|      VALUE | JAN-11 | JAN-12 | FEB-11 | FEB-12 | MAR-11 | MAR-12 | APR-11 |
-----------------------------------------------------------------------------
|      Affec |  30565 |  20955 |  27754 |  19513 |  22838 |  18778 |  20235 |
|        ftm |   5024 |   3518 |   4189 |   4185 |   3282 |   4105 |   2967 |
|        KPI |   82.4 |   93.0 |   76.1 |   93.4 |   79.3 |   95.9 |   84.7 |
| KPIprecent |  34623 |  25915 |  27757 |  25188 |  23758 |  25098 |  21950 |
|        Out |   7380 |   1942 |   8726 |   1774 |   6193 |   1079 |   3967 |
|  rep_in_10 |   7003 |   4754 |   5648 |   5768 |   4394 |   5784 |   3895 |
|      Total |  42003 |  27857 |  36483 |  26962 |  29951 |  26177 |  25917 |