How to concatenate many rows with same ID in SQL PDW/Azure SQL DW

Question

My table contains the details like with two fields. I want to get the the unique details with DisplayName like: I want to know how to do this is Parallel Data Warehouse/APS since the FOR XML PATH function is not supported in PDW.

1 Editor,Reviewer 7 EIC,Editor,Reviewer

  ID      DisplayName
  1        Editor
  1        Reviewer
  7        EIC
  7        Editor
  7        Reviewer
  7        Editor
  19       EIC
  19       Editor
  19       Reviewer

I've tried the following code which would work on a traditional SQL Server but "For XML Path' function is not supported in APS.

        SELECT id, displayname = 
     STUFF((SELECT DISTINCT ', ' + displayname
        FROM #t b 
         WHERE b.id = a.id 
      FOR XML PATH('')), 1, 2, '')
      FROM #t a
      GROUP BY id

Answer 1

If you know a fixed upper count for the number of values to be concatenated, the following technique will work.

create table test1 (id integer,email varchar(255)) with (heap,distribution=round_robin);

insert into test1 (id,email) values (1,'[email protected]');
insert into test1 (id,email) values (1,'[email protected]');
insert into test1 (id,email) values (2,'[email protected]');
insert into test1 (id,email) values (2,'[email protected]');

select id as Id,concat_ws(',',[1],[2],[3],[4]) as EmailAddresses from (
    select id,[1],[2],[3],[4]
    from (
        select id,row_number() over (partition by id order by email) seq,email from (
            select distinct id,email from test1
            ) as distinctRows
        ) as numberedRows
    pivot (
        max(email) for seq in ([1],[2],[3],[4])
        ) as pivotLookup
    ) as pivotedRows