-2

I have data like this

Party_Id    Account_Id  TR_Id
1           1           6
1           2           6
2           3           5
2           4           5
3           6           7
3           7           8

I want result like below

Party   Trading
1        6
2        5
3        7,8
Thom A
  • 88,727
  • 11
  • 45
  • 75
Koala.Et
  • 1
  • 1
  • 1
    Hints: `DISTINCT`, a derived table/CTE, and String Aggregation. – Thom A Oct 25 '22 at 10:42
  • SELECT Party_Id , STRING_AGG(DISTINCT TR_Id) FROM Table – Koala.Et Oct 25 '22 at 10:47
  • `DISTINCT` isn't a valid operator for `STRING_AGG`, and it requires 2 parameters (the string to aggregate, and the delimiter). This is why you need a derived table/CTE. – Thom A Oct 25 '22 at 10:48

1 Answers1

1

You just need to perform any distincting separately:

SELECT Party = Party_Id, Trading = STRING_AGG(TR_Id, ',') 
FROM 
(
   SELECT Party_Id, TR_Id 
   FROM dbo.YourTableName 
   GROUP BY Party_Id, TR_Id
) AS x GROUP BY Party_Id;
Stuck at 1337
  • 1,900
  • 1
  • 3
  • 13