CODEWITHSUNDEEP
sqlsql-server
JJ.
JJ.

Reputation: 9970

Turning certain rows into columns in SQL Server

Please see table below:

For each CustomerID + PaymentID + ClaimNum, there are 3 NULL values for the ItemID & Qty.

I want to take these 3 NULL values and put them as COLUMNS.

DeductionID 1085 = Service Fee (Column Name)

DeductionID 486 = Freight Charge (Column Name)

DeductionID 559 = Handling Charge (Column Name)

For example, let's take the ClaimNum for the highlighted NULLs:

Doing what I want to do, it would end up with the following columns:

CustomerID, PaymentID, ClaimNum, ItemID, Qty, Amount, Service Fee, Freight Charge, Handling Charge

I'm not sure if I need to do a CROSS JOIN, PIVOT or UNPIVOT here. I've never done this in the past and reading Microsoft's website on this isn't really helping. Can anyone give me a hand?

Let me know if you need more info. I appreciate all the help!!

Upvotes: 0

Views: 37

Answers (1)

podiluska
podiluska

Reputation: 51514

You can pivot, then join back to your original data.

ie:

select 
    yourtable.* , Service, handling, freight
from yourtable
    inner join 
(
    select CustomerID, PaymentID, ClaimNum, 
            [1085] as Service, [486] as freight, [559] as handling
    from 
            (select customerid, paymentid, claimnum, DeductionID, Amount from yourtable) t
                pivot 
        (sum (amount) for DeductionID in ([1085],[486],[559]))p
        ) pt 
    on yourtable.CustomerID = pt.CustomerID
    and yourtable.PaymentID = pt.PaymentID
    and yourtable.ClaimNum = pt.ClaimNum
where ItemID is not null

Upvotes: 1

Related Questions