需要有关对此SQL表进行分组的帮助

I have this table

我有这张桌子

I want to group it like

我想把它分组

ProductID       Purchased      Sold
------------------------------------
1               235            0.00
2               125            0.00
3               55             24.00

Here Purchased = (IsCreditor <> 0)

这里购买=(IsCreditor <> 0)

I tried this

我试过这个

SELECT
    ProductID, 
    Max (TotalQty) As Purchased,
    Min(TotalQty) As Sold
FROM
    TestTable
GROUP BY
    ProductID

It will work only if a product have both IsCreditor = 0 and IsCreditor = 1

仅当产品同时具有IsCreditor = 0和IsCreditor = 1时,它才有效

The table is a small part of a stored Procedure

该表是存储过程的一小部分

In this table, for productid = 1, if it is not purchased or sold, it won't appear and if it is purchased it appears with a qty and IsCreditor = 1 and if it is sold, it appears with qty in one row with IsCreditor=0 and one more with isCreditor=1

在此表中,对于productid = 1,如果没有购买或出售,它将不会出现,如果它被购买它出现时带有数量和IsCreditor = 1,如果它被出售,它出现时带有数量的一行IsCreditor = 0,isCreditor = 1还有一个

1 个解决方案

#1

add a CASE statement to separate purchased and sold items

添加CASE语句以分隔购买和出售的商品

    SELECT
        ProductID, 
        SUM(Case When IsCreditor = 1 then TotalQty else 0 end) AS [TotalPurchase],
        SUM(Case When IsCreditor = 0 then TotalQty else 0 end) AS [TotalSale]
    FROM
        TestTable
    GROUP BY
        ProductID