CODEWITHSUNDEEP
sqlsql-server
Kayser Ahmed
Kayser Ahmed

Reputation: 21

How to convert two rows value in a single row?

I have below sql server query data

Sample 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

Upvotes: 2

Views: 63

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

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

Upvotes: 2

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

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

Upvotes: 0

Deepak Sharma
Deepak Sharma

Reputation: 419

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

Upvotes: 0

Related Questions