How to merge two rows if same values in sql server

Question

I have the Following Output:

Sno	Value Stream	Duration	Inspection
1	Test1	3	1
2	ON	14	0
3	Start	5	0
4	Test1	5	1
5	OFF	0	1
6	Start	0	1
7	Test2	0	1
8	ON	3	1
9	START	0	1
10	Test2	2	2

I want to merge the same value after that before START values charge to after ON. For example S.no 4 will merge to s.no4.

1    |    Test1    |      8     |   2      | 

If the combination is not equal then don't allow it to merge. For Example, we have to consider only On/Start. If the condition is OFF/Start then don't allow to merge. E.g. S.no 5 and 6 OFF/Start then don't allow to merge s.no 4 & 7.

Answer 1

I think you are talking about summarization not merging:

select [Value Stream],
min(Sno) as First_Sno,
sum(Duration) as total_Duration,
sum(Inspection) as Inspection
from yourtable
group by [Value Stream]

Will give you the result