Request for assistance - Contract Law for Computers

Can someone good with SQL help write some functions?

Raw event data:
http://34.208.7.206/ContractsPage.aspx

Bad example of 'flattening' that data:
select
Seller = (select top 1 CommerceID from ContractItem where ContractID =
@ContractID and ItemType = 'Offer' order by ItemID),
Buyer = (select top 1 CommerceID from ContractItem where ContractID =
@ContractID and ItemType = 'Agree'),
Amount = (select top 1 Description from ContractItem where ContractID =
@ContractID and ItemType = 'Terms'),
Status = (select top 1 ItemType from ContractItem where ContractID =
@ContractID order by ItemID desc)