SQL-枢转多个列而没有聚集

Question

我不确定如何将数据透视到特定视图。以下是测试数据。

SQL

CREATE TABLE #tmpData (ProductTitle VARCHAR(100), abvrMonthName VARCHAR(3),abvrMonthNameCount VARCHAR(4),MonthAvg NUMERIC(6,2),MonthCount INT)
INSERT INTO #tmpData SELECT 'Product 1','Dec','Dec#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 1','Nov','Nov#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 1','Oct','Oct#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 1','Sep','Sep#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 1','Aug','Aug#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 1','Jul','Jul#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 1','Jun','Jun#',   5   ,   1
INSERT INTO #tmpData SELECT 'Product 1','May','May#',   4.44    ,   9
INSERT INTO #tmpData SELECT 'Product 1','Apr','Apr#',   5   ,   6
INSERT INTO #tmpData SELECT 'Product 1','Mar','Mar#',   5   ,   4
INSERT INTO #tmpData SELECT 'Product 1','Feb','Feb#',   5   ,   1
INSERT INTO #tmpData SELECT 'Product 1','Jan','Jan#',   5   ,   2
INSERT INTO #tmpData SELECT 'Product 2','Dec','Dec#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 2','Nov','Nov#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 2','Oct','Oct#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 2','Sep','Sep#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 2','Aug','Aug#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 2','Jul','Jul#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 2','Jun','Jun#',   5   ,   1
INSERT INTO #tmpData SELECT 'Product 2','May','May#',   4.67    ,   3
INSERT INTO #tmpData SELECT 'Product 2','Apr','Apr#',   4.33    ,   3
INSERT INTO #tmpData SELECT 'Product 2','Mar','Mar#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 2','Feb','Feb#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 2','Jan','Jan#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 3','Dec','Dec#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 3','Nov','Nov#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 3','Oct','Oct#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 3','Sep','Sep#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 3','Aug','Aug#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 3','Jul','Jul#',   0   ,   0
INSERT INTO #tmpData SELECT 'Product 3','Jun','Jun#',   5   ,   3
INSERT INTO #tmpData SELECT 'Product 3','May','May#',   5   ,   6
INSERT INTO #tmpData SELECT 'Product 3','Apr','Apr#',   4   ,   6
INSERT INTO #tmpData SELECT 'Product 3','Mar','Mar#',   4.75    ,   8
INSERT INTO #tmpData SELECT 'Product 3','Feb','Feb#',   4.75    ,   8
INSERT INTO #tmpData SELECT 'Product 3','Jan','Jan#',   4.6 ,   5

SELECT  ProductTitle,[jan],[jan#],[feb],[feb#]
    FROM    
    (   SELECT  *
        FROM    #tmpData        
    ) AS s
    PIVOT
    (
        SUM(MonthAvg) FOR abvrMonthName IN (
            jan,feb,mar,apr,may,jun,jul,aug, sep, oct, nov, [dec]
        )
    ) as p  
    PIVOT
    (
        SUM(MonthCount) FOR abvrMonthNameCount IN (
            jan#,feb#,mar#,apr#,may#,jun#,jul#,aug#, sep#, oct#, nov#, [dec#]
        )
    ) as p1 
    --GROUP BY ProductTitle,[jan],[feb]

DROP TABLE #tmpData

从输出中可以看到，ProductTitle未分组。

我将如何实现这一目标？或者我会完全朝错误的方向前进？

Answer 1

问题的一部分是您已跨多个要透视的列对数据进行了非规范化。理想情况下，您应该考虑修复表结构，以便于维护和查询。如果无法修复表结构，则应先取消对列的透视图，然后应用PIVOT获得最终结果。

UNPIVOT流程将采用多列并将其转换为多行。根据您的SQL Server版本，有几种方法可以执行此操作。可以使用UNPIVOT函数，或者由于使用的是SQL
Server 2008，因此可以将CROSS APPLY与VALUES子句一起使用来取消透视。

交叉申请/值代码为：

select t.producttitle, c.col, c.value
from tmpData t
cross apply
(
  values (abvrMonthName, MonthAvg), (abvrMonthNameCount, MonthCount)
) c (col, value)

请参阅带有演示的SQL
Fiddle
。这将占用您的多列，并将数据放入类似于以下格式的格式：

| PRODUCTTITLE |  COL | VALUE |
-------------------------------
|    Product 1 |  Dec |     0 |
|    Product 1 | Dec# |     0 |
|    Product 1 |  Nov |     0 |
|    Product 1 | Nov# |     0 |
|    Product 1 |  Oct |     0 |
|    Product 1 | Oct# |     0 |
|    Product 1 |  Sep |     0 |
|    Product 1 | Sep# |     0 |

一旦数据采用这种格式，就可以将PIVOT应用于col包含月份名称的值：

select producttitle, jan, [jan#], feb, [feb#], mar, [mar#], apr, [apr#],
  may, [may#], jun, [jun#], jul, [jul#], aug, [aug#],
  sep, [sep#], oct, [oct#], nov, [nov#], dec, [dec#]
from
(
  select t.producttitle, c.col, c.value
  from tmpData t
  cross apply
  (
    values (abvrMonthName, MonthAvg), (abvrMonthNameCount, MonthCount)
  ) c (col, value)
) d
pivot
(
  sum(value)
  for col in (jan, [jan#], feb, [feb#], mar, [mar#], apr, [apr#],
              may, [may#], jun, [jun#], jul, [jul#], aug, [aug#],
              sep, [sep#], oct, [oct#], nov, [nov#], dec, [dec#])
) piv;

请参阅带有演示的SQL Fiddle。结果如下：

| PRODUCTTITLE | JAN | JAN# |  FEB | FEB# |  MAR | MAR# |  APR | APR# |  MAY | MAY# | JUN | JUN# | JUL | JUL# | AUG | AUG# | SEP | SEP# | OCT | OCT# | NOV | NOV# | DEC | DEC# |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|    Product 1 |   5 |    2 |    5 |    1 |    5 |    4 |    5 |    6 | 4.44 |    9 |   5 |    1 |   0 |    0 |   0 |    0 |   0 |    0 |   0 |    0 |   0 |    0 |   0 |    0 |
|    Product 2 |   0 |    0 |    0 |    0 |    0 |    0 | 4.33 |    3 | 4.67 |    3 |   5 |    1 |   0 |    0 |   0 |    0 |   0 |    0 |   0 |    0 |   0 |    0 |   0 |    0 |
|    Product 3 | 4.6 |    5 | 4.75 |    8 | 4.75 |    8 |    4 |    6 |    5 |    6 |   5 |    3 |   0 |    0 |   0 |    0 |   0 |    0 |   0 |    0 |   0 |    0 |   0 |    0 |