SQL Server Performance

Retrieving concatenated data from single field

Discussion in 'SQL Server 2005 General Developer Questions' started by davem, Jul 25, 2011.

  1. davem New Member

    I have a challenging (to me) issue that I cannot find an answer to. I'm sure there is an answer I just haven't found. I have a web application that manages training class data. The issue involves pulling data about optional alternate instructors.

    I have a CLASSES table of information about a training class. Relevant fields include :
    classinfoID int PK
    classID int
    classdate datetime
    studentID int
    instructorID int - primary instructor identification
    altinstructors nvarchar(50) - alternate instructor(s)

    The alternateinstructor field is intended to store up to 5 instructor id's in the format id1, id2, id3... The number of id's will vary by class/date occurance, and will frequently be zero id's. The id's are integers, but are stored concatenated as text.

    An INSTRUCTORS table stores the actual instructor information :
    instructorID int PK
    instructorFirstName nvarchar
    instructorLastName nvarchar

    The idea was to store the multiple id's in the single text type field, and be able to pull a list of instructor names based on the id(s) stored in that field. Since the INSTRUCTORS instructor id's are a number, they don't tie automatically to the altinstructors field in the CLASSES table. I've tried a variety of CAST/CONVERT methods to get a query to work. I either get no rows returned, or a type conversion error.

    The base query I started with is :

    SELECT instructorID FROM instructors
    WHERE instructorID IN (SELECT altinstructors FROM classes WHERE classID = 123)

    This query returns a conversion failure error trying to convert the altinstructors to data type int.

    I then tried :

    SELECT instructorID FROM instructors
    WHERE CAST(instructorID AS nvarchar) IN (SELECT altinstructors FROM classes WHERE classID = 123)

    This query produces no rows even though there is data that should be retrieved if done correctly.

    I haven't found a solution in my reference books or online. I do know that I could store the alternate instructor ids in a separate table, but the single field seemed like a workable option that would reduce the coding in the webapp. I'm hoping there is something simple I am overlooking.

    Any ideas would be greatly appreciated!
  2. FrankKalis Moderator

    Welcome to the forum!
    If I see this correctly, the basic design is fundamentally wrong. You should not store instructors in a concatenated string in an nvarchar column. To solve to immediate problem, you'd have to used dynamic SQL like this (untested):

    DECLARE @SQL nvarchar(MAX)

    SELECT @SQL = 'SELECT instructorID FROM instructors
    WHERE instructorID IN (' +
    altinstructors +
    ')'
    FROM classes WHERE classID = 123

    EXEC (@SQL)

    But you should really aim to get the model right and move altinstructorsto its own entity.
  3. davem New Member

    Thanks for the feedback. I knew this was an unusual method, but thought there might be a way to make it work. It would have made the website coding so much easier. I'll go back to the separate table for the instructors and make it work.

    Thanks, Dave

Share This Page