CODEWITHSUNDEEP
sqlsql-serverpivot
DmitriBodiu
DmitriBodiu

Reputation: 1210

Sql Pivot convert 2 columns into rows

I have a StockLines table in MsSql 2017. It has columns:

 Product, QuantityInStock, ReservedQuantity.
 CocaCola        50                20

Now I need to display that info in 2 rows:

Product      Quantity         Reserved
CocaCola        20              true
CocaCola        30              false

Is it possible to achive using Pivot()? Am I on a correct path? P.S I'm using Entity Framework Core.

Upvotes: 0

Views: 83

Answers (3)

ilyazakharov
ilyazakharov

Reputation: 288

You can use UNPIVOT

SELECT Product,
        Quantity,
        CASE Reserved
             WHEN 'QuantityInStock' THEN 'FALSE'
             ELSE 'TRUE'
        END  AS Reserved
 FROM   pro
        UNPIVOT(
            Quantity FOR Reserved IN (QuantityInStock, ReservedQuantity)
        )    AS t

Another way is without CASE operator, but with subquery and aliases:

 SELECT Product,
        Quantity,
        Reserved
 FROM   (
            SELECT product,
                   QuantityInStock   AS [false],
                   ReservedQuantity  AS [true]
            FROM   pro
        ) AS tt
        UNPIVOT(Quantity FOR Reserved IN ([false], [true])) upvt

Upvotes: 0

Caius Jard
Caius Jard

Reputation: 74605

A simple to understand approach would be UNION:

SELECT product, reservedquantity as quantity, 'true' as reserved FROM stocklines
UNION ALL
SELECT product, quantityinstock-reservedquantity, 'false' FROM stocklines

I am wondering though if you have other lines in the table, detailing other reservations of the same product, are they also supposed to come from the total? Is the stock quantity the same across all lines? Hopefully all individual reservations of products are grouped together so stock lines table contains one row per product (or things get messy)

You also mentioned you're using ef core- see https://learn.microsoft.com/en-us/ef/core/querying/raw-sql

This technique could also be modified into a LINQ statement, perhaps something like(untested):

context.StockLines.Select(s => new[] { new {
    s.Product,
    Quantity = s.QuantityInStock - s.ReservedQuantity,
    Reserved = false
  },
  new {
    s.Product,
    Quantity = s.ReservedQuantity,
    Reserved = true
  }
}).SelectMany().ToList()

The idea being we take each record and split it into an anonymous type representing the reserved and a type representing the unreserved as an array output from the first select then use selectmany to flatten the enumerable-of-arrays-of-type to an enunerable-of-type

This latter solution might need some tweaks; totally untested

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269523

You can easily do this using apply:

select t.product, v.quantity, v.reserved
from t cross apply
     (values (t.ReservedQuantity, 'true'),
             (t.QuantityInStock - t.ReservedQuantity, 'false')
     ) v(quantity, reserved);

I think you could do this using unpivot, but that is superfluous syntax. apply implements lateral joins, which are very powerful. This is just one example of how they can be used.

Upvotes: 2

Related Questions