Skip to content
Advertisement

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 abc@gmail.com abc@gmail.com 1 01-01-2022
Action test testata 1 01-01-2022

Desired Output :

Email Reason Action Translated Action Translated Reason activityid createdon
abc@gmail.com 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.

Advertisement

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;
5 People found this is helpful
Advertisement