What is wrong with my Pivot Table using SQL Server

Question

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

I tried SQL Query

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.

Please explain with proper reason

Thanks

Answer 1

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

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

So:

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 

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.

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.

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.

Answer 2

You do not require group by inside subquery, you can query as below:

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