SQL - SUM the values from a COLUMN and put each value in a SEPARATE column-CodePudding

I have a simple join query for multiple tables that looks like this:

select
  b.buyer_id,
  v.vendor_id,
  r.report_number,
  sum(r.amount_fee),
  f.fee_description
from buyer b
join vendor v on v.vendor_id = bu.vendor_id
join report r on r.report_num = bu.report_num
join fees f on f.report_num = r.report_num
group by b.buyer_id, v.vendor_id, r.report_number, f.fee_description

Which will show something like this:

But I want it to look like this:

Can you help me how to rewrite my script please?

Thanks alot!

CodePudding user response：

If your fee descriptions are fixed, a simple PIVOT query will do the trick.

SELECT
    buyer_id,
    vendor_id,
    report_number,
    [Late Charge], 
    [Finance Charge], 
    [Reserve Fee], 
    [Additional Fee]
FROM
(
    select b.buyer_id, v.vendor_id, r.report_number, r.amount_fee As AmountFee, f.fee_description 
    from buyer b
    join vendor v on v.vendor_id = bu.vendor_id
    join report r on r.report_num = bu.report_num
    join fees f on f.report_num = r.report_num
) As s
PIVOT
(
    Sum(AmountFee) FOR fee_description IN ([Late Charge], [Finance Charge], [Reserve Fee], [Additional Fee])
)

If the fee descriptions could change, you'll need a dynamic pivot query:

DECLARE @columns nvarchar(max);
SET @columns = STUFF
(
    (SELECT DISTINCT N','   QUOTENAME(fee_description)
     FROM fees
     ORDER BY fee_description
     FOR XML PATH(''), TYPE)
    .value('.', 'nvarchar(max)'),
   1, 1, N''
);

DECLARE @sql nvarchar(max);
SET @sql = N'SELECT buyer_id, vendor_id, report_number, '   @columns   N'
FROM 
(
    select b.buyer_id, v.vendor_id, r.report_number, r.amount_fee As AmountFee, f.fee_description 
    from buyer b
    join vendor v on v.vendor_id = bu.vendor_id
    join report r on r.report_num = bu.report_num
    join fees f on f.report_num = r.report_num
) As s
PIVOT
(
    Sum(AmountFee) FOR fee_description IN ('   @columns   N')
)';

EXEC sp_executesql @sql;