SQL Query for multiple Pivot on same column

As per my requirement, I need to apply pivot twice on same column to get different values as column header. Please see below how the data is present in table and expected results.

Table

Question	Response	TranslatedResponse	activityid	createdon
Reason	Testing	testaus	1	01-01-2022
EMail	[e-mail]	[e-mail]	1	01-01-2022
Action	test	testata	1	01-01-2022

Desired Output :

Email	Reason	Action	Translated Action	Translated Reason	activityid	createdon
[e-mail]	Testing	test	testata	testaus	1	01-01-2022

I have achieved this by using below query where it works by using inner join with two different pivot select statement.

SELECT A.activityid,
       A.createdon,
       A.[Email],
       A.[Action],
       A.[Reason],
       B.[Translated Action],
       B AS [Translated Reason]
FROM (SELECT *
      FROM (SELECT activityid, createdon, question, response FROM Table) subquery
      PIVOT (MAX(response)
             FOR question IN ([Email], [Reason], [Action])) pv1) A
     INNER JOIN (SELECT activityid,
                        createdon,
                        question,
                        translatedresponse,
                        [Action] AS [Translated Action],
                        [Reason] AS [Translated Reason]
                 FROM (SELECT activityid,
                              createdon,
                              question,
                              response,
                              translatedresponse
                       FROM Table) subquery
                 PIVOT (MAX(translatedresponse)
                        FOR question IN ([Email], [Action], [Reason])) pv2) B ON A.activityid = B.activityid;

Is there another way of writing SQL query to optimize and get rid of inner join.

Answer

Use conditional aggregation:

SELECT MAX(CASE Question WHEN 'Email' THEN Response END) AS Email,
       MAX(CASE Question WHEN 'Reason' THEN Response END) AS Reason,
       MAX(CASE Question WHEN 'Action' THEN Response END) AS Action,
       MAX(CASE Question WHEN 'Reason' THEN TranslatedResponse END) AS TranslatedReason,
       MAX(CASE Question WHEN 'Action' THEN TranslatedResponse END) AS TranslatedAction,
       activityid,
       createdon
FROM dbo.YourTable
GROUP BY activityid,
         createdon;