I have a data extension with values like:

Email_Address | Job_ID  | Date    | Etc
wmcm@me.com   | 1234    | 1/11/19 | etc
wmcm@me.com   | 5678    | 1/11/19 | etc
wmcm@me.com   | 9432    | 1/11/19 | etc

Using SQL, I want to pivot this flatten into a single row and have all Jobs as a string in 1 field :

Email_Address   |  All_Jobs 
wmcm@me.com     | 1234, 5678, 9432

My approaches so far of using the SQL Pivot function fails as an unsupported MC Function.

Any ideas how this could be done ?


You could try doing a XML Path on a subquery and wrap it in a STUFF to create your delimited string.

Basically what this will do is pull in each value via the subquery and xml path and then STUFF will turn it into a comma delimited string (or whatever delimiter you want).

It surprisingly is efficient and quick so it can slide into your query without heavily increasing run time / processing pull as some other solutions can be.


SELECT  a.PrimaryKey
        , DelimitedString =
        STUFF((SELECT DISTINCT ', ' + b.MultipleValueField
               FROM (
                        SELECT   sub.PrimaryKey
                               , sub.MultipleValueField
                        FROM [yourSourceDE] sub
                    ) b
               WHERE b.PrimaryKey = a.PrimaryKey
         FOR XML PATH('')), 1, 2, '')
FROM [yourSourceDE] a

I added in DISTINCT to only display unique values. If you want ALL values - including duplicates, you can remove the DISTINCT and it should pull in every value.

| improve this answer | |

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.