Hi there, here is a piece of code that wll do what you wanted:

As you are first time poster, i took the onus on my side to create DDLs for your request; going further, please provide full ready-to-use DDLs so that many volunteers here will give you tested code back 🙂

Now , sample data and table :

DECLARE @table TABLE

(

[Name] varchar(15),

[Order] varchar(15),

[Month] varchar(15),

[Detail] varchar(15),

[Value] numeric(10,2),

[Margin] numeric(10,2)

)

insert into @table([Name], [Order] ,[Month] ,[Detail] ,[Value] ,[Margin])

select 'Fred' ,'Ord1', 'Month2', 'Ace', 1300, 400

union all select 'Fred' ,'Ord2', 'Month2' ,'Long', 0 ,-15010

union all select 'Fred' ,'Ord3', 'Month2' ,'Bing', 2270 ,309.09

union all select 'Fred' ,'Ord4', 'Month1' ,'Stuff', 6471.6, 5383.81

union all select 'Fred' ,'Ord4', 'Month2' ,'Stuff', 3275, -462.79

union all select 'Fred' ,'Ord5', 'Month2' ,'Roof' ,4940 ,1140.72

union all select 'Fred' ,'Ord6', 'Month2' ,'Wall' ,12545, 2110.37

Now the code that will provide you the output:

;with cte as

(

select [Name], [Order] ,[Month] ,[Detail] ,[Value] ,[Margin] ,

row_number() over(partition by [Order] order by [Order]) rn from @table

),

only_one as

(

select [order] from cte group by [order] having count(*) = 1

),

multiple as

(

select * from cte where [order] in (select distinct [Order] from cte where rn > 1)

)

select [Name], [Order] ,[Month] ,[Detail] ,[Value] ,[Margin] from cte

where [order] in (select [order] from only_one)

union all

select t1.[Name], t1.[Order] ,t1.[Month] ,t1.[Detail] ,t1.[Value]-t2.[Value] [Value]

, t1.[Margin] -t2.[Margin] [Margin]

from multiple t1

cross join

multiple t2

where t1.rn = 2 and t2.rn = 1