0

I need to concatenate several columns in an alphabetical order. Is that possible to be accomplish with Marketing Cloud limited SQL?

Below you can find the sample structure:

SELECT TOP 1
constraintl, constraint2, constraint3, constraint4
FROM OCP_CONTACT_EVENT_REASON_DETAIL_constraints
ORDER BY constraintl, constraint2, constraint3, constraint4 ASC

Output

constraintl constraint2 constraint3 constraint4
dddd aaaa ffff bbbb
1
  • Do you mean concatenate the values of the fields alphabetically? Commented May 10 at 13:12

1 Answer 1

1

If you're wanting to sort and concatenate all of the field values, you can do something like this:

CREATE TABLE OCP_CONTACT_EVENT_REASON_DETAIL_constraints (
      field1 varchar(10)
    , constraintl varchar(10)
    , constraint2 varchar(10)
    , constraint3 varchar(10)
    , constraint4 varchar(10)
);
INSERT INTO OCP_CONTACT_EVENT_REASON_DETAIL_constraints
     (field1,constraintl, constraint2, constraint3, constraint4)
VALUES
     ('whee!','dddd','aaaa','ffff','bbbb');
select * from OCP_CONTACT_EVENT_REASON_DETAIL_constraints;

select
field1,
stuff(x.c,1,1,'') as constraints
from OCP_CONTACT_EVENT_REASON_DETAIL_constraints
cross apply (

  select top 4 ',' + rtrim(y.c) from (

    select constraintl as c from OCP_CONTACT_EVENT_REASON_DETAIL_constraints
    union all
    select constraint2 as c from OCP_CONTACT_EVENT_REASON_DETAIL_constraints
    union all
    select constraint3 as c from OCP_CONTACT_EVENT_REASON_DETAIL_constraints
    union all
    select constraint4 as c from OCP_CONTACT_EVENT_REASON_DETAIL_constraints

  ) as y
  order by y.c
  FOR XML PATH('')

) x(c)

field1 constraintl constraint2 constraint3 constraint4
whee! dddd aaaa ffff bbbb
field1 constraints
whee! aaaa,bbbb,dddd,ffff

Try it yourself: dbfiddle

1
  • You're lifesaver! :) as I don't need this field1 column, here's what I currently got and it returns null from the Data Extension (however all those columns are not empty). May I ask you to help me assigned your script accordingly?
    – MrSkover
    Commented May 10 at 18:26

You must log in to answer this question.

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