Reputation: 31
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 protected] | [email protected] | 1 | 01-01-2022 | |
Action | test | testata | 1 | 01-01-2022 |
Desired Output :
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
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