How can I do "double-aggregation" in T-SQL / SQL Server?

I have a table which contains Issues and SubIssues in a customer service system. The data might look like this:

I'd like to aggregate total response times for each ticket_id satisfying each of the conditions at a time. For example the resulting table may look like this (but assume N conditions)

Here it is in pseudo code if I could do Python+SQL:

foreach ticket_number in issues_and_subissues:
  foreach condition in cond1, cond2, cond3, ... condN:
    SELECT sum(response_time) FROM issues_and_subissues isst WHERE isst.ticket_id=ticket_number AND condition=1

Is there a T-SQL-only way to achieve this via JOINs? or would I have to use CURSERS? any other options?

I searched on Stackoverflow and beyond and all I could find was the CURSER way of doing it. Thank you!

We can sum the condition flag multiplied by the response time. This answer assumes that the condition flags would only ever be 0 or 1. If not, then this answer would have to change.

<!-- language: sql -->

SELECT
    ticket_id,
    SUM(cond1 * response_time_in_min) total_resp_time_cond_1,
    SUM(cond2 * response_time_in_min) total_resp_time_cond_2,
    SUM(cond3 * response_time_in_min) total_resp_time_cond_3
FROM yourTable
GROUP BY ticket_id
ORDER BY ticket_id;

It's called conditional aggregation. You do it by putting a CASE expression inside an aggregate function:

SELECT ticket_id,
    SUM(CASE WHEN cond1 = 1 THEN response_time_in_min ELSE 0 END) total_resp_time_cond_1,
    SUM(CASE WHEN cond2 = 1 THEN response_time_in_min ELSE 0 END) total_resp_time_cond_2,
    SUM(CASE WHEN cond3 = 1 THEN response_time_in_min ELSE 0 END) total_resp_time_cond_3   
FROM [table]
GROUP BY ticket_id

If i understand you correctly, you want to create "conditions" out of thin air. This is usually how i do it:

SELECT
    ticket_id,
    SUM(CASE WHEN cond = 1 THEN response_time_in_min END) total_resp_time_cond_1,
    SUM(CASE WHEN cond = 2 THEN response_time_in_min END) total_resp_time_cond_2,
    SUM(CASE WHEN cond = 3 THEN response_time_in_min END) total_resp_time_cond_3
FROM yourTable t
CROSS APPLY (
   SELECT 1 AS cond
   WHERE t.somefield = 1
   AND t.anotherfield = 2
   UNION ALL
   SELECT 2 AS cond
   WHERE t.somefield = 2
   AND t.anotherfield = 3
   SELECT 3 AS cond
   WHERE t.somefield = 3
   AND t.anotherfield = 4
 ) c
GROUP BY ticket_id
ORDER BY ticket_id;

Alternative:

SELECT
    ticket_id,
    SUM(CASE WHEN t.somefield = 1
   AND t.anotherfield = 2 THEN response_time_in_min END) total_resp_time_cond_1,
    SUM(CASE WHEN t.somefield = 2
   AND t.anotherfield = 3 THEN response_time_in_min END) total_resp_time_cond_2,
    SUM(CASE WHEN t.somefield = 3
   AND t.anotherfield = 4 THEN response_time_in_min END) total_resp_time_cond_3
FROM yourTable t
GROUP BY ticket_id
ORDER BY ticket_id;

Both ways have it's pros and cons, depending on how you wanna aggregate stuff. Sometimes it's useful to create own row for each condition, in this case CROSS APPLY version can be good.