0

I have a multi-leveled table hierarchy in SQL Server, and when joining them I want to do an aggregation of strings from rows of one of the tables. In my (simplified) example in the screenshot below, I have Level1->Level2->Level3 and also a table PerformingUser "hanging under" Level1. I have done a JOIN on all these four tables, resulting in 8 rows. So far all is fine.

Now, what I want is that each of this 8 rows gets a new column ("AllUsersForLevel1") with an aggregation of all PerformingUsers for its Level1. (The intent is that the PerformingUser also can see what other PerformingUsers have the same Level1.)

So for the first 2 rows I want a new column with 'aretha, mary'. And in the other 6 rows that column should have 'john, jim'.

I have tried STRING_AGG (as indicated in the comment in the image), but it requires a grouping (which I do not want; I want all 8 rows). I also tried to do a STRING_AGG on a OVER (PARTITION BY Level1ID), but got "The function 'STRING_AGG' is not a valid windowing function, and cannot be used with the OVER clause."

Does anyone have advice on how to do this?

Screenshot from SSMS

user736570
  • 469
  • 4
  • 15
  • Look at this [dbfiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=b97154d88c642fdc061abab890038326). It's for Oracle, but should help. – Ponder Stibbons Jul 01 '20 at 21:55
  • @PonderStibbons: Looks like I cannot mark a commen as accepted answer. If you add an answers, I'll mark it as accepted :-) – user736570 Jul 02 '20 at 07:55
  • My solution was for Oracle, I only suggested subquery. Post your code as answer and accept it if you want. – Ponder Stibbons Jul 02 '20 at 10:24

0 Answers0