代码之家 › 专栏 › 技术社区 › ravsun

转置SQL Server查询结果

pivot tsql sql-server

ravsun · 技术社区 · 7 年前

我有一张桌子,上面有:

销售量
总销售额
价格区间销售数据

这是我的桌子:

Year || Item_Category || Dollar_Volume || No_of_Sales || $0-$99  || $100-$199 || $200-$299
2018 || Produce       ||    2359       ||     23      ||   13    ||    4      ||    6
2018 || Clothing      ||    4325       ||     118     ||   21    ||   70      ||   27
2018 || Fruits        ||    3756       ||     19      ||   15    ||    3      ||    1
2018 || Vegetables    ||    9124       ||     64      ||   19    ||    0      ||   45
2018 || Packed Food   ||    1174       ||     91      ||    7    ||   67      ||   17
2018 || Detergent     ||     568       ||     103     ||   99    ||    4      ||    0
2018 || Entertainment ||    6127       ||     925     ||   32    ||  500      ||  393

我需要改变这个,这样预期的结果是:

Year ||               || Produce || Clothing || Fruits || Vegetables || Packed Food || Detergent || Entertainment
2018 || Dollar_Volume ||    2359 ||   4325   ||  3756  ||    9124    ||    1174     ||   568     ||   6127
2018 || No_of_Sales   ||      23 ||    118   ||    19  ||      64    ||      91     ||   103     ||    925
2018 || $0-$99        ||      13 ||     21   ||    15  ||      19    ||       7     ||    99     ||     32
2018 || $100-$199     ||       4 ||     70   ||     3  ||       0    ||      67     ||     4     ||    500
2018 || $200-$299     ||       6 ||     27   ||     1  ||      45    ||      17     ||     0     ||    393

我正在尝试 PIVOT 下面给出的查询:

select [Year],[Produce],[Clothing],[Fruits],[Vegetables],[Packed Food],[Detergent],[Entertainment]
from 
(SELECT [Year],[Item_Category],[Total Sales],[Dollar Volume],[$0 to $99],[$100 to $199],[$200 to $299] 
FROM RAVI_PROJECTS.dbo.YTD_Sales_Data
) s
PIVOT 
(MAX([$0 to $99]),MAX([$100 to $199]),MAX([$2000 to $2999])) 
for [Item_Category] in ([Produce],[Clothing],[Fruits],[Vegetables],[Packed Food],[Detergent],[Entertainment])
) p;

但是,我在“pivot”旁边的行中得到了错误(“:

MSG 102,15级,状态1,第105行
“,”附近有语法错误。

2 回复 | 直到 7 年前

Andrea 7 年前

您可以使用 pivot 具有 cross apply :

declare @tmp table (Year int, Item_Category nvarchar(100), Dollar_Volume int,
                    No_of_Sales int, [$0-$99] int, [$100-$199] int, [$200-$299] int)
insert into @tmp values
    (2018 , 'Produce'      , 2359, 23 , 13,   4,   6),
    (2018 , 'Clothing'     , 4325, 118, 21,  70,  27),
    (2018 , 'Fruits'       , 3756, 19 , 15,   3,   1),
    (2018 , 'Vegetables'   , 9124, 64 , 19,   0,  45),
    (2018 , 'Packed Food'  , 1174, 91 ,  7,  67,  17),
    (2018 , 'Detergent'    ,  568, 103, 99,   4,   0),
    (2018 , 'Entertainment', 6127, 925, 32, 500, 393)

select piv.Year, piv.COL as [Column], piv.Produce, piv.Clothing, piv.Fruits,
       piv.Vegetables, piv.[Packed Food], piv.Detergent, piv.Entertainment
from 
( 
    select [Item_Category] , COL,VAL, [YEAR], ORD
    from @tmp 
    CROSS APPLY (VALUES 
        ('Dollar_Volume',[Dollar_Volume],1), 
        ('No_of_Sales'  ,[No_of_Sales]  ,2), 
        ('$0-$99'       ,[$0-$99]       ,3), 
        ('$100-$199'    ,[$100-$199]    ,4), 
        ('$200-$299'    ,[$200-$299]    ,5)
    )CS (COL,VAL,ORD) 
) src 
pivot ( max(val) for [Item_Category] in ([Produce], [Clothing], [Fruits], [Vegetables], 
        [Packed Food], [Detergent], [Entertainment]) ) piv 
order by piv.ORD

结果:

Alex Yena 7 年前

我看到你使用的表格已经是Pivot了。我建议您取消透视它,而不是将它透视到另一个。它应该起作用