CODEWITHSUNDEEP
sql-server
Rasik
Rasik

Reputation: 2420

Using Pivot on sql to get the result

I have the table structure as below:

create table temp
(
    storeid int,
    txncount int,
    txntype nvarchar(10),
    paymode nvarchar(10),
    amount money
)

insert into temp values ('1099','1560', 'Buy','Cash', 1000.00)
insert into temp values ('1099','1', 'Sell' ,'Bank', 500.00)
insert into temp values ('1099','1618','Sell', 'Cash', 800.00)
insert into temp values ('1099','1','Sell', 'Cheque', 700.00)
insert into temp values ('1099','1','Sell', 'Wallet', 1100.00)

I have been struggling to display the result as expected.

Expected Output

I think i need to use the PIVOT to get the above result. But i could not distribute the payment mode as shown in the column in the result.

I have used pivot as below. But could not get the idea how to select the column. 

(
sum(Amount)
FOR  paymode in(Cash,Bank,Cheque,Wallet)
) AS R
group by      
    storeid

Upvotes: 0

Views: 74

Answers (2)

Ross Bush
Ross Bush

Reputation: 15185

You can also pivot data without using PIVOT.

DECLARE @temp TABLE
(
    storeid int,
    txncount int,
    txntype nvarchar(10),
    paymode nvarchar(10),
    amount money
)

insert into @temp values ('1099','1560', 'Buy','Cash', 1000.00)
insert into @temp values ('1099','1', 'Sell' ,'Bank', 500.00)
insert into @temp values ('1099','1618','Sell', 'Cash', 800.00)
insert into @temp values ('1099','1','Sell', 'Cheque', 700.00)
insert into @temp values ('1099','1','Sell', 'Wallet', 1100.00)



SELECT 
    storeid, 
    SellCount =SUM(CASE WHEN txntype = 'Sell' THEN txncount ELSE NULL END),
    SellCash = SUM(CASE WHEN txntype = 'Sell' AND paymode = 'Cash' THEN amount ELSE NULL END),
    SellCash = SUM(CASE WHEN txntype = 'Sell' AND paymode = 'Bank' THEN  amount ELSE NULL END),
    SellChecque = SUM(CASE WHEN txntype = 'Sell' AND paymode = 'Cheque' THEN  amount ELSE NULL END),
    SellWallet = SUM(CASE WHEN txntype = 'Sell' AND paymode = 'Wallet' THEN  amount ELSE NULL END),
    BuyCount = SUM(CASE WHEN txntype = 'Buy'  THEN txncount ELSE NULL END),
    BuyCash = SUM(CASE WHEN txntype = 'Buy' AND paymode = 'Cash' THEN amount ELSE NULL END),
    BuyCash = SUM(CASE WHEN txntype = 'Buy' AND paymode = 'Bank' THEN amount ELSE NULL END),
    BuyChecque = SUM(CASE WHEN txntype = 'Buy' AND paymode = 'Cheque' THEN amount ELSE NULL END),
    BuyWallet = SUM(CASE WHEN txntype = 'Buy' AND paymode = 'Wallet' THEN amount ELSE NULL END)
FROM
    @temp
GROUP BY
    storeid

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

This seems to be close to what you're asking for. We first do some type conversions and an unpivot so that all of the values to be pivotted are in a single column, then synthesize the final column names and perform the pivot:

declare @temp table
(
    storeid int,
    txncount int,
    txntype nvarchar(10),
    paymode nvarchar(10),
    amount money
)
insert into @temp values
('1099','1560', 'Buy','Cash', 1000.00),
('1099','1', 'Sell' ,'Bank', 500.00),
('1099','1618','Sell', 'Cash', 800.00),
('1099','1','Sell', 'Cheque', 700.00),
('1099','1','Sell', 'Wallet', 1100.00)

select
    *
from
    (select
        storeid,txntype + CASE WHEN type='txncount' THEN 'Count'
                               ELSE paymode END as newtxntype,
        value
    from
        (select storeid,CONVERT(decimal(18,4),txncount) as txncount,txntype,paymode,
                        CONVERT(decimal(18,4),amount) as amount from @temp) t
            unpivot ([value] for [type] in (txncount,amount)) u
    ) x
        pivot
            (SUM(value) for newtxntype in (SellCount,SellCash,SellBank,SellCheque,
                      SellWallet,
                               BuyCount,BuyCash,BusBank,BuyCheque,BuyWaller)) y

Result:

storeid     SellCount                               SellCash                                SellBank                                SellCheque                              SellWallet                              BuyCount                                BuyCash                                 BusBank                                 BuyCheque                               BuyWaller
----------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1099        1621.0000                               800.0000                                500.0000                                700.0000                                1100.0000                               1560.0000                               1000.0000                               NULL                                    NULL                                    NULL

Upvotes: 1

Related Questions