SQL-总枢轴列 [英] SQL - Total Pivot Column

问题描述

对于以下sql脚本，我想在末尾添加一个总计"列，以对每一行的列中的数据求和.

for the following sql script, I would like to add a "Total" column at the end to sum the data in the columns for each row.

请帮助:

select * from 
(SELECT       Calender.YrPer, [Period DATA 6b].PRODUCT_NO, isnull(sum([Period DATA 6b].LI_QTY+[Period DATA 6b].RG_QTY*1.0),0) as "TotalUnits"
FROM            Calender INNER JOIN
                         [Period DATA 6b] ON Calender.[Starting Period] = [Period DATA 6b].STARTING_PERIOD LEFT OUTER JOIN
                         Allproducts ON [Period DATA 6b].PRODUCT_NO = Allproducts.PRODUCT_NO
where Calender.YrPer >= '2012P05'
group by Calender.YrPer, [Period DATA 6b].PRODUCT_NO)

as test1
pivot
(
sum(TotalUnits)
for YrPer
in ([2012P05],[2012P06],[2012P07],[2012P08],[2012P09],[2012P10],[2012P11],[2012P12],[2012P13],[2013P01],[2013P02],[2013P03],[2013P04],[2013P05],[2013P06],[2013P07],[2013P08],[2013P09],[2013P10],[2013P11],[2013P12],[2013P13],[2014P01],[2014P02],[2014P03],[2014P04])
)
PivotTable
order by PRODUCT_NO

所需的样本输出:

PRODUCT_NO  2012P05 2012P06 2012P07 2012P08 2012P09 2012P10 2012P11 2012P12 2012P13     2013P01 2013P02 2013P03 2013P04 2013P05 2013P06 2013P07 2013P08 2013P09 2013P10 2013P11     2013P12 2013P13 2014P01 2014P02 2014P03 2014P04 2013 R52    2014 R52
75  551 506 473 261 557 1037    285 250 278 234 NULL    NULL    NULL    NULL    NULL        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL        4432    0
174 NULL    NULL    NULL    NULL    1   NULL    NULL    NULL    NULL    NULL    NULL        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL        NULL    NULL    NULL    NULL    1   0
216 NULL    NULL    0   0   0   0   NULL    NULL    NULL    NULL    NULL    NULL        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL        NULL    NULL    NULL    0   0

推荐答案

使用您的代码，添加并加查询以仅按PRODUCT_NO获得总组. YrPer设置为常量"TotalPerProduct"，并在数据透视表中使用此常量. 由于您未包含任何样本数据.我刚刚添加了代码，我认为它应该可以工作.

Using your code, add union plus query to get total group by only PRODUCT_NO. The YrPer set to constant "TotalPerProduct" and use this constant in the pivot. Since you did not include any sample data. I just added the code and I think it should work.

尝试..

    select * from 
(SELECT       Calender.YrPer, [Period DATA 6b].PRODUCT_NO, isnull(sum([Period DATA 6b].LI_QTY+[Period DATA 6b].RG_QTY*1.0),0) as "TotalUnits"
FROM            Calender INNER JOIN
                         [Period DATA 6b] ON Calender.[Starting Period] = [Period DATA 6b].STARTING_PERIOD LEFT OUTER JOIN
                         Allproducts ON [Period DATA 6b].PRODUCT_NO = Allproducts.PRODUCT_NO
where Calender.YrPer >= '2012P05'
group by Calender.YrPer, [Period DATA 6b].PRODUCT_NO
UNION
SELECT       'TotalPerProduct' YrPer, [Period DATA 6b].PRODUCT_NO, isnull(sum([Period DATA 6b].LI_QTY+[Period DATA 6b].RG_QTY*1.0),0) as "TotalUnits"
FROM            Calender INNER JOIN
                         [Period DATA 6b] ON Calender.[Starting Period] = [Period DATA 6b].STARTING_PERIOD LEFT OUTER JOIN
                         Allproducts ON [Period DATA 6b].PRODUCT_NO = Allproducts.PRODUCT_NO
where Calender.YrPer >= '2012P05'
group by [Period DATA 6b].PRODUCT_NO

)

as test1
pivot
(
sum(TotalUnits)
for YrPer
in ([2012P05],[2012P06],[2012P07],[2012P08],[2012P09],[2012P10],[2012P11],[2012P12],[2012P13],[2013P01],[2013P02],[2013P03],[2013P04],[2013P05],[2013P06],[2013P07],[2013P08],[2013P09],[2013P10],[2013P11],[2013P12],[2013P13],[2014P01],[2014P02],[2014P03],[2014P04], [TotalPerProduct])
)
PivotTable
order by PRODUCT_NO

这篇关于SQL-总枢轴列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！