1

I am performing a SQL query in Automation Studio and I want the following:

Populate a data extension with ID of the Account_salesforce table with the ListName (Lists) bound to the ID of the first table.

For example: Has Account.Id that has Many Lists.

Account.Id
1
Lists
List_1,
List_2,
List ....,

In my searches I only found something close to what I want to do using PIVOT, but the Cloud Marketing SQL is not supported.

Below is my query.

SELECT    Account.Id AS 'ID SF'
            , ls.listName AS 'List'
    FROM _ListSubscribers AS ls
    LEFT JOIN [Account_Salesforce] AS Account 
    ON Account.PersonEmail = ls.EmailAddress AND Account.name = ls.SubscriberKey
    WHERE 
    ls.listName IN ('List_1'
                    ,'List_2')
    GROUP BY 
        Account.Id, ls.EmailAddress,ls.listName
  • what is the use case in having the rows ordered in the target DE? if using in ampscript you can use lookuporderrows() function, if you are extracting the info you can order it in the external system – EazyE May 6 at 19:02
  • Hi @EazyE Why do I need to update a list that matches the ID in Salesforce CRM? Or seka I'm taking this information from the Cloud Marketing to Salesforce. I have not thought about using AMPScript, I will try to understand how lookuporderrows works () – Arthur Santos May 6 at 19:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.