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

BJones · Accepted Answer · 2019-12-23T20:45:40.030

0

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,

edited Dec 23 '19 at 20:45

answered Dec 23 '19 at 20:12

BJones

2,450
2
17
25

Still does only show 5 in the [in inventory] need to show at least 9 different records see my screenshot of me just selecting the inventory items – Jan Moenens Dec 23 '19 at 20:15
Then I would bet that one of the other columns should be removed. Remove `WHERE pa.AvatarName = 'Jedi'` and post the sample data. Here's a [SQL Fiddle](http://sqlfiddle.com/#!18/269b5/3/0) that depicts it should work as long as you have the correct groupings. – BJones Dec 23 '19 at 20:25
sorry mate im pretty new"noob" in sql and don't realy understand what you want in that fiddle. If you wanne see my project check my github https://github.com/janmoenens/database everything is there but all my writing is in dutch. but if you go into my examenDatabase folder everything you need shoud be there also if you like you can build it youreself. this project im making is for my sql exam – Jan Moenens Dec 23 '19 at 20:30
Looking at your github data... For `PlayerId = 31` there are three different `AvatarName` values - Jedi, Jedi2, Jedi3... And three different `Type`s - WARLOCK, HUNTER, WARRIOR... If this is the same data that you're using, this would be one of the problems I see. See my edit above. – BJones Dec 23 '19 at 20:45
Ty indeed this works a bit more then expected but it works :D ty – Jan Moenens Dec 23 '19 at 20:51

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

1 Answers1