CODEWITHSUNDEEP
sqlsql-serverjoinpivot
sarika1810
sarika1810

Reputation: 31

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 [email protected] [email protected] 1 01-01-2022
Action test testata 1 01-01-2022

Desired Output :

Email Reason Action Translated Action Translated Reason activityid createdon
[email protected] 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.

Upvotes: 0

Views: 495

Answers (1)

Thom A
Thom A

Reputation: 96055

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;

Upvotes: 3

Related Questions