KoldCoffee (5/1/2013)
Chris,thanks for reply. I should have posted what the result set was that I'm after. There should be two rows for each person, one recording total count of tickets and the other total duration of tickets. It should look like this:
Name, Category, Value
Helen | Count | 2
Helen |Duration| 28
Joe | Count | 2
Joe |Duration| 33
Blake | Count | 2
Blake |Duration| 62.
If Helen had another ticket, then count would go up to 3 and Duration would increase.
;with ct1 as (
select
name
,Value = COUNT(ticketID)
, Category = 'Count'
from #temp
group by Name
),
ct2 AS (
select
name
, Value= (Select sum(Duration))
, Category = 'Duration'
from #temp
group by name
)
SELECT *
FROM ct1
UNION ALL
SELECT *
FROM ct2
ORDER BY Name
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden