SQL: How to make multiple row data into single row

Question

I wish to make my data from :-

Customer Product TransactionType Quantity Value
  A       ItemA        Sale         1      10
  A       ItemA        Refund       1      10
  C       ItemB        Sale         1       5

To following structure:-

Customer Product SaleValue SaleQuantity RefundValue RefundQuantity 
   A      ItemA        10          1           10           1
   C      ItemB         5          1            0           0

I just want to make TransactionType & Quantity in order to split into individual txn type column & its own quantity. I tried to use MSSQL Pivot function, but it does not suit my need. I don't expect full answer, but at least I need some ideas on this. Thanks.

Do a GROUP BY. Use case expressions to do conditional aggregation. — jarlh
– jarlh, Commented Mar 4, 2020 at 9:47

Yogesh Sharma · Accepted Answer · 2020-03-04 09:49:48Z

1

You can do aggregation :

select customer, product, 
       sum(case when transactiontype = 'sale' then value else 0 end) as salevalue,
       sum(case when transactiontype = 'sale' then Quantity else 0 end) as saleqty,
       sum(case when transactiontype = 'Refund' then value else 0 end) as Refundvalue,
       sum(case when transactiontype = 'Refund' then Quantity else 0 end) as Refundqty
from table t
group by customer, product

answered Mar 4, 2020 at 9:49

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Biegeleisen · Accepted Answer · 2020-03-04 09:49:46Z

0

Use pivoting logic:

SELECT
    Customer,
    Product,
    MAX(CASE WHEN TransactionType = 'Sale'   THEN Value END) AS SaleValue,
    MAX(CASE WHEN TransactionType = 'Sale'   THEN Quantity END) AS SaleQuantity,
    MAX(CASE WHEN TransactionType = 'Refund' THEN Value END) AS RefundValue,
    MAX(CASE WHEN TransactionType = 'Refund' THEN Quantity END) AS RefundQuantity
FROM yourTable
GROUP BY
    Customer,
    Product;

answered Mar 4, 2020 at 9:49

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Collectives™ on Stack Overflow

SQL: How to make multiple row data into single row

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related