Distinct inside String_Agg

Question

I am making a school assignment, though I am finished with it since I can't use distinct inside a String_Agg. But I get the 'SNS' more than once, so I was thinking if there is a way to distinct it so I only got it once.

I tried distinct, but found out it didn't work with string_agg, right now I am trying with a subquery, but I don't know how to do it.

select FAG$.FagID, FagNavn, STRING_AGG(Lektion$.LaererID, ',') 'Lærer'
from Fag$
join Lektion$
on Fag$.FagID= Lektion$.FagID
group by Fag$.FagID, FagNavn, LaererID

I expect the output to be:

"NET = KBEN, SNS"

example, instead of

"NET = KBEN"
"NET = SNS, SNS, SNS"

i get duplicates, so i want to get rid of the duplicates, if it is possible? :)

Answer 1

Your GROUP BY should have the unaggregated columns in the SELECT. STRING_AGG() is an aggregation function. So you probably want:

select f.FagID, FagNavn, STRING_AGG(l.LaererID, ',') as Lærer
from Fag$ f join
     Lektion$ l
     on f.FagID = l.FagID
group by f.FagID, FagNavn;

If you still duplicates (which seems unlikely with an id, then you need to use a subquery of some sort to remove the duplicates.