I recon Lynn's code can be simplified.

Since both CTE's use the same grouping they may be merged in one:

with cte as (

select

Name,

count(Name) as [Count],

sum(Duration) as Duration

from #temp

group by Name

)

select Name, 'Count' as Category, [Count] as [Value]

from cte

union all

select Name, 'Duration' as Category, Duration as [Value]

FROM cte

order by Name;

go

But since the code in CTE is re-executed each time the CTE is referenced I have to support Lynn here - CTE does not provide any advantage.

Except, probably, more pleasant look of the query.

If you have many categories to display you better go with a temp table in place of CTE.

Then the base table will be queried just once.

And all UNION parts will be reading the #temp resultset cached in memory.