Reputation: 29
I am using SQL Server and I want to data according to posid
in each column here is my code:
select ItcodedGrp,ItheadCat, ItheadCls,ItheadGrp,POsid,
(sum(OpnQty) + sum(FaultyQty) + sum(UsedQty) + sum(RecQty) - sum(RejectQty) - sum(Issued) +
sum(DebitQty) + sum(AdjQty) + sum(STNQtyPls) - sum(STNQtyMin) - sum(POSQTY) - sum(TransitQty) - sum(FQtyMin)
+ sum(FQtyPlus) + sum(ReplaceQty)) as AllStock
from Vmgt_RSales_Stock
where POsid in (2,6,7,10,11,12,13,14) and ItcodedGrp like '28.01.059.%'
group by ItcodedGrp,ItheadCat, ItheadCls,ItheadGrp,POsid
order by POsid asc
and here is my result.
I want the stock according to posid
in each column
as you can see in above image my stock is showing in front of posid
but I want the all stock in column but posid
wise like let me give you an example the how my result should be like the below
Upvotes: 1
Views: 54
Reputation: 222722
You can use conditional aggregation. Your sum()
expression is lenghty so I would add one level of aggregation to avoid repeating it many times:
select
ItcodedGrp,
ItheadCat,
ItheadCls,
ItheadGrp,
sum(AllStock) AllStock,
sum(case when POSid = 2 then AllStock end) POSid2,
sum(case when POSid = 6 then AllStock end) POSid6,
sum(case when POSid = 7 then AllStock end) POSid7,
sum(case when POSid = 10 then AllStock end) POSid10,
sum(case when POSid = 11 then AllStock end) POSid11,
sum(case when POSid = 12 then AllStock end) POSid12,
sum(case when POSid = 13 then AllStock end) POSid13,
sum(case when POSid = 14 then AllStock end) POSid14
from (
select
ItcodedGrp,
ItheadCat,
ItheadCls,
ItheadGrp,
POsid,
(
sum(OpnQty)
+ sum(FaultyQty)
+ sum(UsedQty)
+ sum(RecQty)
- sum(RejectQty)
- sum(Issued)
+ sum(DebitQty)
+ sum(AdjQty)
+ sum(STNQtyPls)
- sum(STNQtyMin)
- sum(POSQTY)
- sum(TransitQty)
- sum(FQtyMin)
+ sum(FQtyPlus)
+ sum(ReplaceQty)
) as AllStock
from Vmgt_RSales_Stock
where
POsid in (2,6,7,10,11,12,13,14)
and ItcodedGrp like '28.01.059.%'
group by
ItcodedGrp,
ItheadCat,
ItheadCls,
ItheadGrp,
POsid
) t
group by
tcodedGrp,
ItheadCat,
ItheadCls,
ItheadGrp
Upvotes: 1