Sql-server – Unknown number of potential values in a table from a foreign table

database-designsql server

I can't seem to figure out the best way to do this…

Table A                   Table B
    Id                    Id
    TableBId              ValueNeeded
                          TableAId

So I Table A needs values from Table B, where Table B's 'TableAId' is matching the id.

Example 

TableA                   TableB
_________________________________________________________
ID        TableBId       Id     ValueNeeded    TableAId
1         ???            1      'Foo'          1
                         2      'Bar'          1

I need Table A to pull both value's from table B, ('Foo','Bar'). but the complicated part is that Table A can only have one column to return multiple values. And I foresee up to nearly 30 potential rows needed from Table B, for Table A. I think I need a lookup table in between, but does said lookup table need to have 30 columns? and what if a use case requires more than those 30 columns? I need this to work somehow

Edit: clarity – I'm creating a web app for myself and some friends to use for Dungeons and Dragons, this is for the 'Proficiencies' portion of the player character generation. Every player gets different proficiencies based on class, race, and level. So a level 1 player might only have 8-15 proficiencies, whereas a level 20 player could have dozens they've amassed. What I'm trying to accomplish is: One player(TableA) with ideally, one column, that points to a reference table(TableB), that then (ideally) has a few rows (race, class) that then pulls from another table (TableC) to finally fill out all the proficiencies that the player will have. The more I think about it the more sure I am that I need to make the reference table with a LOT of nullable columns….

Best Answer

Every character has one and only one race, right? So it seems like that field should be on the Character table: TableA(?) Each character could have multiple classes so that should probably be in a one-to-many relationship in the CharacterClass table which has the primary key of both Character and Class tables.

As for the Proficiencies (TableB), each character can have zero to many proficiencies so they must be stored in their own table, but if you want to produce a 'report' which shows all the proficiencies as a delimited list, then you could do it like this:

Select c.id,
    proficiencies = Stuff((Select ',' + proficiency 
                             From #character_proficiency As cp
                             Where cp.character_id = c.Id
                             For XML Path ('')), 1, 1, '')
  From #character As c;
Related Question