[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 697
  • Last Modified:

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.
  • 2
1 Solution
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
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 member.name from leader,member, leaderxmember where leaderxmember.leaderid = leader.leaderid and leader.name='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.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now