I'm trying to write this query and the idea is to look at the field "MessageName" and when that field value is equal to 'Text 1 Response', I want to copy the field value from another field called MessageText and write that value as Text1Response in the target data extension called "Responses"
If the MessageName value is "Text 2 Response" then I want to write the MessageText value into Text2Response in the target data extension.
SELECT Date, MessageName, MessageType, MobileNumber, CASE MessageName WHEN MessageName = 'Text 1 Response' THEN MessageText AS Text1Response WHEN MessageName = 'Text 2 Response' THEN MessageText AS Text2Response END FROM Responses_Report
I wrote up this query but I'm getting an error: "Incorrect syntax near '='."
I've been digging around online but haven't found anything.