0

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
Kamran
  • 147
  • 4
  • 14

1 Answers1

0

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,'abc@msn.com');
insert into test1 (id,email) values (1,'xyz@gmail.com');
insert into test1 (id,email) values (2,'efg@xyz.com');
insert into test1 (id,email) values (2,'efg@xyz.com');

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
Ron Dunn
  • 2,971
  • 20
  • 27
  • concat_ws does not work in PDW. any other solutions? – Kamran Apr 25 '19 at 14:12
  • I'm very sorry, Kamran. Your question title clearly mentioned PDW, but I got an alert for Azure SQL Data Warehouse and didn't notice the difference. You're right, concat_ws is not supported. I think your only option is the technique linked by wBob under your question. It will be slow, but it will work. – Ron Dunn Apr 25 '19 at 14:15