Concatenating text fields from multiple records in to one string

We have a Caseload system where a teacher can assign certain specific kids to their caseload, so with one set of menu buttons they can access any kid in the district, with another they access only a filtered list.  These are the kids that they are supposed to pay special attention to.  To complicate matters, because they can never be simple, it was decided that it was OK that one kid could be on multiple caseloads.  Okay, not a difficult change, except that I displayed the caseload teacher’s name on the bottom of the student edit screen.  Now I had multiple teachers and needed to build a string so they could all be shown together.

This is something that I’ve done before, but I couldn’t find the code that I needed.  I remembered the basics and eventually found a sample that let me build out the code that worked.

This is the basic code:

DECLARE @s AS VARCHAR(40); --MUST be varchar!  Doesn’t work if it’s char.
SET @s = '';
SELECT @s = @s + t.TeacherName + ', '
FROM Caseload cl
JOIN Teachers t
       ON cl.TeacherID = t.TeacherID
WHERE cl.StudentID = 1
ORDER BY t.TeacherName;
SELECT SUBSTRING(@s, 1, LEN( @s) - 1);
SELECT @s;

It builds up the @s variable, holding all the teacher names, for StudentID 1.  Then the substring removes the last comma and space.

First, our test rig.  Two tables, Teachers and Caseload.  We’ll keep it basic with a half dozen teachers and we’ll just use three students.  No student table because we’re just concerned with an integer student ID number.

/****** Object:  Table [dbo].[Teachers] ******/
CREATE TABLE [dbo].[Teachers](
       [TeacherID] [int] NOT NULL,
       [TeacherName] [varchar](10) NOT NULL,
 CONSTRAINT [PK_Teachers] PRIMARY KEY CLUSTERED
(      [TeacherID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
/****** Object:  Table [dbo].[Caseload] ******/
CREATE TABLE [dbo].[Caseload](
       [TeacherID] [int] NOT NULL,
       [StudentID] [int] NOT NULL,
 CONSTRAINT [PK_Caseload] PRIMARY KEY CLUSTERED
(      [TeacherID] ASC, [StudentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
--Populate the two tables:
INSERT [dbo].[Teachers] ([TeacherID], [TeacherName]) VALUES (1, N'Bob');
INSERT [dbo].[Teachers] ([TeacherID], [TeacherName]) VALUES (2, N'Carol');
INSERT [dbo].[Teachers] ([TeacherID], [TeacherName]) VALUES (3, N'Ted');
INSERT [dbo].[Teachers] ([TeacherID], [TeacherName]) VALUES (4, N'Alice');
INSERT [dbo].[Teachers] ([TeacherID], [TeacherName]) VALUES (5, N'Woody');
INSERT [dbo].[Teachers] ([TeacherID], [TeacherName]) VALUES (6, N'Buzz');
INSERT [dbo].[Caseload] ([TeacherID], [StudentID]) VALUES (1, 1);
INSERT [dbo].[Caseload] ([TeacherID], [StudentID]) VALUES (1, 2);
INSERT [dbo].[Caseload] ([TeacherID], [StudentID]) VALUES (2, 1);
INSERT [dbo].[Caseload] ([TeacherID], [StudentID]) VALUES (3, 1);
INSERT [dbo].[Caseload] ([TeacherID], [StudentID]) VALUES (4, 1);
INSERT [dbo].[Caseload] ([TeacherID], [StudentID]) VALUES (5, 2);
INSERT [dbo].[Caseload] ([TeacherID], [StudentID]) VALUES (6, 3);
--Show the joined caseloads so we can more easily see what the UDF will produce:
SELECT cl.StudentID, cl.TeacherID, t.TeacherName
FROM Caseload cl
JOIN Teachers t
       ON cl.TeacherID = t.TeacherID
ORDER BY cl.StudentID, cl.TeacherID;
--same code as above demonstrating the concatenation
DECLARE @s AS VARCHAR(40);
SET @s = '';
SELECT @s = @s + t.TeacherName + ', '
FROM Caseload cl
JOIN Teachers t
       ON cl.TeacherID = t.TeacherID
WHERE cl.StudentID = 1
ORDER BY t.TeacherName;
SELECT SUBSTRING(@s, 1, LEN( @s) - 1);
SELECT @s;
*/
--The UDF that does everything needed and returns the populated string
ALTER FUNCTION dbo.udf_GetCaseloads (@SeekerID INT)
RETURNS VARCHAR(80)
AS
BEGIN
       DECLARE @s AS VARCHAR(80);
       SET @s = '';
       SELECT @s = @s + t.TeacherName + ', '
       FROM Caseload cl
       JOIN Teachers t
              ON cl.TeacherID = t.TeacherID
       WHERE cl.StudentID = @SeekerID
       ORDER BY t.TeacherName;
       --make sure there's data in the var before trying substrings!
       IF LEN(RTRIM(@s)) > 0
              SET @s = (SUBSTRING(@s, 1, LEN(@s) - 1));
       RETURN @s;
END;
--test harness for the above:
SELECT dbo.udf_GetCaseloads(1) AS CaseloadList; --test UDF
SELECT dbo.udf_GetCaseloads(2) AS CaseloadList; --test UDF
SELECT dbo.udf_GetCaseloads(3) AS CaseloadList; --test UDF
SELECT dbo.udf_GetCaseloads(4) AS CaseloadList; --test UDF

Unfortunately I wasn’t able to get this code working in Access VBA.  My final solution was to wrap it in to a stored procedure which updated a record that’s always available.  I’ll show that tomorrow.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s