0

I made a survey in the Sales Cloud that I am pulling into the Marketing Cloud using automation studio and the responses are saved in a data extension. However every question response appears as a different row. I am trying to use a SQL Query to join all responses from the same respondent into a new data extension. My query is as follows:

SELECT 
  a.ResponseShortText firstquestion, 
  b.ResponseShortText secondquestion, 
  a.Id Id
FROM 
  Survey_Object_Import_Intern a
INNER JOIN 
  Survey_Object_Import_Intern b 
ON 
  b.ResponseId = a.ResponseId

I created a data extension with 3 column names: firstquestion, secondquestion, Id. The syntax is valid, but when I run the query it does not do anything. (I selected the target data extension in the query activity in automation studio and selected append).

Improve this question
5
  • Have you verified that there are matching ResponseIDs in the two DEs? I see that you have a.Id as a field, is that the same as ResponseID?
    – Gortonington
    Jul 1, 2019 at 19:45
  • @Gortonington those are different IDs. Id is the primary key, ResponseId is what I am trying to join them by: i.e. I submit a survey with 3 different questions- each response ends up in a different row with a different Id, but they all have the same ResponseId as they were submitted by the same person.
    – Intern
    Jul 1, 2019 at 19:49
  • I am not quite understanding it. But if it is not erroring but is displaying 0 results, I would check on the INNER JOIN as if there are no matches, it will return 0 results.
    – Gortonington
    Jul 1, 2019 at 19:52
  • did you query error? check the activity log tab within the query
    – EazyE
    Jul 1, 2019 at 20:13
  • @EazyE yes it did: primary key violation, it says that a key was duplicated and it was the first one in the database: >2.1 SQL Query Survey_SQL_Activity_Intern >ERROR: Query failed during execution. Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object 'Survey_SQLed_DE_Intern'. The duplicate key value is (0Kr1L00000001B5SAI). View Activity Details
    – Intern
    Jul 1, 2019 at 20:17

1 Answer 1

Reset to default
0

I talked to Salesforce Support and they told me that this was not possible. Their response was the following:

This situation is not able to be accomplished through our SQL Activities because it would be accomplished through a For Loop. Our SQL activities prevent looping behavior because it could allow for problematic queries to become a larger issue.

Instead of scrubbing the data in this way, I recommend correcting the behavior at the source, on the Object itself so that each 'ResponseShortText' input is added as ''ResponseShortText1', ''ResponseShortText2'', 'ResponseShortText3', and etc.

Before hearing back from support I had been attempting to solve this in other ways. My latest attempt (and somewhat succesful) was through this SQL Query Activity that I placed in Automation Studio.

SELECT b.ResponseShortText ResponseShortText2, a.Id, a.ResponseShortText
FROM Survey_Object_Import_Intern b
INNER JOIN Survey_DE_Intern_2 a
ON a.ResponseId = b.ResponseId

Context for the code:

I had two import activities that were calling the same object, one however, I selected the ResponseShortText to be of class 'Email Address'. This allowed me to have one data extension with just the emails and another with everything (including emails). The query worked for a survey with only two different survey questions. I ran into issues when trying to re-run the import activity as I had added an extra row 'ResponseShortText2' (set to null) and had manually changed it to sendable and testable.

Hope this can help somebody.

Short Answer:

It's not possible.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .