T-SQL STRING_AGG problems dunno if bad writing or just not working

Question

How come i don't see the seperation off my i.inventoryId in first part ? but when i select them in a other select i get way more results. also 'Jedi' == (PlayerAvatarId 6)

https://i.ibb.co/XW4NJC7/image.png

SELECT p.FirstName [Spelers Voornaam]
    ,p.LastName [Spelers Achternaam]
    ,pa.AvatarName [Spelers Avatarnaam]
    ,pa.FamilyName [Familie's Groeps Naam]
    ,Av.Type [Avatar's Type]
    ,string_agg (i.InventoryId, ',') as [In Inventory]

FROM Player AS p
LEFT JOIN PlayerAvatar AS pa ON p.PlayerId = pa.PlayerId
LEFT JOIN Avatar AS Av ON pa.AvatarId = Av.AvatarId
LEFT JOIN Avatar AS a ON pa.AvatarId = a.AvatarId
LEFT JOIN Inventory as i on  i.InventoryId = pa.InventoryId
LEFT JOIN Item as it on it.ItemId = i.ItemId
WHERE pa.AvatarName = 'Jedi'
GROUP BY p.FirstName, p.LastName, pa.AvatarName, pa.FamilyName, av.Type, i.InventoryId

SELECT *
from Inventory
Where PlayerAvatarId = 6

Answer 1

I don't think you want to group by InventoryId if that's what you're concatenating... Try this:

Edit, you need to remove columns that are different from row-to-row.

SELECT p.FirstName [Spelers Voornaam]
    ,p.LastName [Spelers Achternaam]
    ,pa.FamilyName [Familie's Groeps Naam]
    ,string_agg (i.InventoryId, ',') as [In Inventory]
FROM Player AS p
LEFT JOIN PlayerAvatar AS pa ON p.PlayerId = pa.PlayerId
LEFT JOIN Avatar AS Av ON pa.AvatarId = Av.AvatarId
LEFT JOIN Avatar AS a ON pa.AvatarId = a.AvatarId
LEFT JOIN Inventory as i on  i.InventoryId = pa.InventoryId
LEFT JOIN Item as it on it.ItemId = i.ItemId
GROUP BY p.FirstName, p.LastName, pa.AvatarName, pa.FamilyName, av.Type

Or you can aggregate those columns too.

SELECT p.FirstName [Spelers Voornaam]
    ,p.LastName [Spelers Achternaam]
    ,string_agg(pa.AvatarName,',') [Spelers Avatarnaam]
    ,pa.FamilyName [Familie's Groeps Naam]
    ,string_agg(Av.Type,',') [Avatar's Type]
    ,string_agg (i.InventoryId, ',') as [In Inventory]

FROM Player AS p
LEFT JOIN PlayerAvatar AS pa ON p.PlayerId = pa.PlayerId
LEFT JOIN Avatar AS Av ON pa.AvatarId = Av.AvatarId
LEFT JOIN Avatar AS a ON pa.AvatarId = a.AvatarId
LEFT JOIN Inventory as i on  i.InventoryId = pa.InventoryId
LEFT JOIN Item as it on it.ItemId = i.ItemId
GROUP BY p.FirstName, p.LastName, pa.FamilyName,