CODEWITHSUNDEEP
sqlsql-servert-sqlpivot
Makhdoom Liaqat
Makhdoom Liaqat

Reputation: 29

One column data into multiple column

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

pos wise stock in seaprate 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

Please see the below

Upvotes: 1

Views: 54

Answers (1)

GMB
GMB

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

Related Questions