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

Posted on 2007-10-11
Last Modified: 2012-05-05
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.
Question by:bigtwig
    LVL 18

    Expert Comment

    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 ...
    LVL 8

    Expert Comment

    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.
    LVL 2

    Author Comment

    Could you show this to me in some pseudo-graphic layout so I can understand it better (and what links them together)?  Thanks
    LVL 8

    Accepted Solution

    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.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Suggested Solutions

    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now