当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - How to convert two rows value in a single row?

问题描述:

I have below sql server query data

Looking for solution.

SQL Query:

SELECT

p.ProjectName,

i.ItemName,

inv.TransactionDirection,

SUM(inv.TransactionQty) AS TransactionQuantity

FROM INVTransaction inv

JOIN BDProject p ON p.ProjectID=inv.ProjectID

JOIN MDItem i ON i.ItemID=inv.ItemID

GROUP BY p.ProjectName,

i.ItemName,

inv.TransactionDirection

网友答案:

I think you just want conditional aggregation:

SELECT p.ProjectName, i.ItemName,
       SUM(CASE WHEN inv.TransactionDirection = 'IN' THEN inv.TransactionQty ELSE 0 END) as IN_Quantity,
       SUM(CASE WHEN inv.TransactionDirection = 'OUT' THEN inv.TransactionQty ELSE 0 END) as OUT_Quantity,
       SUM(CASE WHEN inv.TransactionDirection = 'IN' THEN inv.TransactionQty
                WHEN inv.TransactionDirection = 'OUT' THEN -inv.TransactionQty 
                ELSE 0
           END) as Balance
FROM INVTransaction inv JOIN
     BDProject p
     ON p.ProjectID = inv.ProjectID JOIN
     MDItem i ON i.ItemID = inv.ItemID
GROUP BY p.ProjectName, i.ItemName
网友答案:

You can use Pivot for this. Below is working query.

SELECT PROJECTNAME,ITEMNAME,[IN],[OUT] ,ISNULL([IN],0)-ISNULL([OUT],0) AS BALANCE FROM
(SELECT PROJECTNAME,ITEMNAME,TRANSACTIONDIRECTION,TRANSACTIONQUANTITY FROM TRANSACTIONS
)A
PIVOT (SUM(TRANSACTIONQUANTITY) FOR TRANSACTIONDIRECTION IN ([IN],[OUT])) AS PVT

Replace TRANSACTIONS with your table name

网友答案:

As @Gordon Linoff almost provided solution but for balance column you can replace

SUM(inv.TransactionQty) as Balance

With

SUM(CASE WHEN inv.TransactionDirection = 'IN' 
         THEN inv.TransactionQty 
         ELSE -1*inv.TransactionQty END) as Balance
分享给朋友:
您可能感兴趣的文章:
随机阅读: