Setting up an array in SQL Server to store from 3-10 items.

How do I setup an array in SQL Server when a field might have 3 items or might have 10 items?
I need to create a dropdown list that will populate from a query.  The user will then select somewhere between 3-10 people that he wants on his team.  How do I allow for that in SQL Server?

There are about 20 other fields on this form that need to be created for each record.
Who is Participating?
jamcosConnect With a Mentor Commented:
I can do data to illustrate...hopefully that will do it as my graphics ability in ASCII is pretty weak.
Leader table  (id, name)
1, Jeff
2, Joe
Member table (id, name):
10, Jane
11, Mary
12, Helen

That sets up the basic tables and you can populate the dropdownlist of members from the Member table. Now, to store a team, you have a third table.
LeaderXMember (leader id, memberid):
1, 10 (Jane is on Jeff's team)
1, 11 (Mary is on Jeff's team)
2, 11 (Mary is on Joe's team)
2,12 (Helen is on Joe's team)

So, to display a team, you need to join the LeaderXMember table to the Leader and Member tables to get the names. If I want to display the members of Jeff's team, I use something like:
select from leader,member, leaderxmember where leaderxmember.leaderid = leader.leaderid and'Jeff' and member.memberid = leaderxmember.memberid
I'd generally have a leaderid handy instead of finding a team by the leader's name, and that would remove the leader table from the join.
SQL Server does not support actual arrays.
You can either create a temporary table, use a common table expression or (ab)use a varchar data type to enter a comma separated array (or any other separator of course)

Hope this helps ...
To support this type of multiple linking, I use a cross reference table. For eaxmple, I have a table of leaders: leader with columns of LeaderId, LeaderName. Then I have my selection table Members with columns MemberId and MemberName. To allow a leader to have several members, I have another table that links the 2: LeaderXMember with columns LeaderId, MemberId. That way, I'm not limited to 3, 10 or 1000.
bigtwigAuthor Commented:
Could you show this to me in some pseudo-graphic layout so I can understand it better (and what links them together)?  Thanks
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.