Once again, you really don't need to use CTEs in this example. I am posting my code again, but this time I have included a version using CTEs. If the order of the data from each CTE is also important, add a "rank" column like Matt Miller did in his code and include that in the ORDER BY clause along with the Name column.

The "rank" column can be added to each individual query (my original code) or in the CTEs.

create table #temp (

TicketID int,

Name varchar(20),

Duration int

)

insert into #temp

values (10, 'Helen', 8),

(15, 'Blake', 12),

(20, 'Joe', 17),

(25, 'Joe', 16),

(30, 'Blake', 50),

(35, 'Helen', 20);

go

select

Name,

'Count' as Category,

count(Name) as Value

from

#temp

group by

Name

union all

select

Name,

'Duration' as Category,

sum(Duration) as Value

from

#temp

group by

Name

order by

Name;

go

with cte1 as (

select

Name,

'Count' as Category,

count(Name) as Value

from

#temp

group by

Name

), cte2 as (

select

Name,

'Duration' as Category,

sum(Duration) as Value

from

#temp

group by

Name

)

select * from cte1

union all

select * FROM CTE2

order by

Name;

go

drop table #temp;

go