I think you're looking for something like this.

;with ct1 as

(select

name

,Duration, 2 as rank

from #temp

), ct2 as

(

select

name

, sum(Duration) as value

, 'Duration' as category,

, 1 as rank

from ct1

group by name

)

select

name

, Category

, Value

, null as Memo

from ct2

union all

select name,

'Count' as category,

value,

null as memo

from CTE1

order by name,rank

A few notes:

- because you're doing a union (even if it's a union all), you need to be disciplined to make sure that both parts have the same names and that they're in the same order within the query.

- using the = in a select to create an alias is being deprecated, you really want to avoid that notation if possible.

- the rows won't just order themselves without you telling them HOW to be ordered correctly, so structure the order by accordingly. Remember that there is no such thing as physical or natural order in databases, so you have to be explicit about the ordering.