Turning certain rows into columns in SQL Server

Question

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!!

Can you show us an example of your desired result?

Andrew
– Andrew

2014-03-04 16:05:10 +00:00
Commented Mar 4, 2014 at 16:05 — Andrew
– Andrew, Commented Mar 4, 2014 at 16:05

podiluska · Accepted Answer · 2014-03-04 16:17:46Z

1

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

answered Mar 4, 2014 at 16:17

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Turning certain rows into columns in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related