Combine multiple rows into single column

Transact-SQL
1

Hi,

I have table
PONumber------ChargeAmount----Type
ABC123-----------1000---------------Freight
ABC123------------500----------------Freight
ABC123------------200----------------Miscellaneous
ABC123------------100----------------Miscellaneous

need output in record to be inserted into another table, Type - Freight should be added up to charges and type -Miscellaneous should be added to AdditionalCharges and AdditionalChargetype.

PONumber-------Charge-------AdditionalCharge-------AdditionalChargeType
ABC123---------1500-----------300-------------------------Miscellaneous

Any help is appreciated. thanks

2

please provide sample data in the following manner

declare @sample(
PONumber varchar(10), 
ChargeAmount money, 
Type varchar(10)
)
insert into @sample
select 'ABC123', 1000, 'Freight' union
select 'ABC123', 500, 'Freight'

please fill in the rest. help us help you

3

Sure, I changed the sample data
declare @sample table (
PONumber varchar(10),
ChargeAmount money,
[Type] varchar(20)
)
insert into @sample
Values( 'ABC123', 1000, 'Freight'), ( 'ABC123', 500, 'Freight'),('ABC123', 200, 'Miscellaneous' ),
('ABC123', 100, 'Miscellaneous')

thanks

4

now if you have any other type other than Miscellaneous and Freight then you will need something different but with just those 2 here is one way of doing it

;with src
as
(
select PONumber, 
sum(ChargeAmount) as AdditionalCharge, 
Type as AdditionalChargeType
 From @sample
 where [Type] = 'Miscellaneous'
 group by PONumber, Type
)
select s.PONumber, 
sum(ChargeAmount) as Charge, 
AdditionalCharge, 
AdditionalChargeType
 From @sample s
 join src on  s.PONumber = src.PONumber
 where [Type] = 'Freight'
 group by s.PONumber, AdditionalCharge, AdditionalChargeType
1 Like
5 
SELECT
    PONumber,
    SUM(CASE WHEN Type <> 'Miscellaneous' THEN ChargeAmount ELSE 0 END) AS Charge,
    SUM(CASE WHEN Type =  'Miscellaneous' THEN ChargeAmount ELSE 0 END) AS AdditionalCharge,
    'Miscellaneous' AS AdditionalChargeType
FROM @sample
GROUP BY PONumber
ORDER BY PONumber
1 Like
6

thank you, it work perfectly

7

thanks it worked

8

go with @ScottPletcher, it is cleaner