使用SQL Server的数据透视表有什么问题? [英] What is wrong with my Pivot Table using SQL Server?

问题描述

我想将 TotalQtyParent 的行记录显示为一周的列

I wants to display the row record of TotalQtyParent into column as a weeks

select 
    ItemLookupCode,
    StoreID,
    DepartmentID,
    Weeks,
    TotalQtyParent
from 
    #finalResult
where 
    ItemLookupCode = '610759C2000' 
order by StoreID

我尝试了SQL查询

select itemlookupcode, storeid, departmentid,[30],[31] from 
     (
        select 
            fr.itemlookupcode,
            fr.storeid,
            fr.departmentid,
            fr.totalqtyparent,
            fr.asofweekonhand,
            fr.weeks
        from 
            #finalresult fr
        Group By
            fr.itemlookupcode,
            fr.storeid,
            fr.departmentid,
            fr.totalqtyparent,
            fr.asofweekonhand,
            fr.weeks
    ) x

    pivot 
    (
        sum(totalqtyparent)
        for weeks in ([30],[31])
    ) p 

但是输出有些错误，并且显示了重复的行

But the output is some thing wrong and it shows the duplicate rows

我不知道为什么它显示重复的行.

I cannot figure out why it shows the duplicate rows.

请以正当理由进行解释

谢谢

推荐答案

您可能会注意到，PIVOT执行聚合.使用GROUP BY功能可以执行SQL中更简单的聚合形式.

As you may notice, a PIVOT performs aggregation. Simpler forms of aggregation in SQL are performed using the GROUP BY feature.

在PIVOT中，有一个隐式 GROUP BY，它覆盖了结果集中当前的所有其他列.

In a PIVOT, there is an implicit GROUP BY, covering all other columns currently in the result set.

所以:

select itemlookupcode, storeid, departmentid,[30],[31] from 
 (
    select 
        fr.itemlookupcode,
        fr.storeid,
        fr.departmentid,
        fr.totalqtyparent,
        fr.asofweekonhand,
        fr.weeks
    from 
        #finalresult fr
    Group By
        fr.itemlookupcode,
        fr.storeid,
        fr.departmentid,
        fr.totalqtyparent,
        fr.asofweekonhand,
        fr.weeks
) x
---Point of pivot
pivot 
(
    sum(totalqtyparent)
    for weeks in ([30],[31])
) p 

在上面标记的枢轴点"上，我们正在使用的结果集包含6列-itemlookupcode，storeid，departmentid，totalqtyparent，asofweekonhand和. PIVOT使用totalqtyparent和weeks.这意味着其他四列的行为就像被GROUP BY编辑一样-itemlookupcode，storeid，departmentid和asofweekonhand的每个唯一组合将产生一个输出行.

At the "point of pivot" I've marked above, the result set we're working with contains 6 columns - itemlookupcode, storeid, departmentid, totalqtyparent, asofweekonhand and weeks. The PIVOT uses totalqtyparent and weeks. This means that the other four columns act as if they're being GROUP BYed - each unique combination of itemlookupcode, storeid, departmentid and asofweekonhand is going to produce one output row.

在SELECT子句中没有提及asofweekonhand都没关系-PIVOT作为FROM子句的一部分，在逻辑上首先被处理.

It doesn't matter that you don't mention asofweekonhand in the SELECT clause - the PIVOT is logically processed first, as part of the FROM clause.

因此，如果您不希望将列视为此隐式GROUP BY"的一部分，则需要在引入PIVOT之前将其从结果集中中删除子句-要么不首先引入它(如 Kannan的答案所示)，要么将整个现有查询转移到一个子查询，可以从中查询较小的列子集.

So, if you don't want a column to be considered as part of this "implicit GROUP BY", you need to eliminate that column from the result set before introducing the PIVOT clause - either by not introducing it in the first place (as shown in Kannan's answer) or by shifting your entire existing query into a subquery from which a smaller subset of columns can be SELECTed.

这篇关于使用SQL Server的数据透视表有什么问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！