1

I'm currently working on a project of my own that uses an SQL DB to store character information for an RPG game. I'm trying to write a select statement to retrieve the information related to the equipment the user is using, at the moment I have:

SELECT 
CE.CharacterID, CE.EquipmentSlotID, CE.ItemID, I.ItemTypeID, I.Name, I.Image, IT.Name, IT.Description,  concat(IA.AttributeID, ' ', IA.value )
FROM 
CharacterEquipment CE INNER JOIN Item I 
ON CE.ItemID = I.ItemID
INNER JOIN ItemType IT
ON I.ItemTypeID=IT.ItemTypeID
INNER JOIN ItemAttribute IA
ON I.ItemID=IA.ItemID
WHERE CE.CharacterID = 1
ORDER BY CE.ItemID ASC;

This returns this:

enter image description here

My problem is that each item (ItemID) can have multiple attributes (AttributeID) and I want all these attributes to appear on a single line but I'm not sure how I'd go about combining the results

EDIT: Sorry guys, should have said, I've been experimenting with the group concat function but SQL isnt my strong suit so im having bother writing it and figuring out how it works, any chance any one could explain it abit more indepth? Thanks

Michael Tourish
  • 15
  • 5
  • 4
    Possible duplicate of [SQL group\_concat function in SQL Server](http://stackoverflow.com/questions/8868604/sql-group-concat-function-in-sql-server) – Lukasz Szozda Nov 09 '15 at 07:34
  • Btw, you should normalize your table. It seems like there is some redundancy in `Description` field. Think about which object does this information describe and consider storing it alongside with that object. – Kamil Gosciminski Nov 09 '15 at 07:48
  • @ConsiderMe the multiple descriptions are because the select statement returns the several attributes tied to that item (Note: ItemID 1 fior the first 3 rows with different attributes) this is what im trying to fix, for these attributes and associated values to be concatented into a single string, unless im misunderstanding you. – Michael Tourish Nov 10 '15 at 01:50
  • @MichaelTourish you could have a dictionary to store every combination of possible descriptions and thus avoid redundancy in your main table. – Kamil Gosciminski Nov 10 '15 at 11:05

1 Answers1

0
  1. Possible solution to this would be improving your database structure and try denormalizing it.

2.But if you wish to continue with same structure try using Concatenation using COALESCE function For your reference :Concatenate many rows to one

aditya Kohli
  • 1