Concatenate column values from rows

Question

I am using a timetabling system with SQL Server 2000 backend I need to list events with tutors and rooms next to them this can be more than 1 so could do with turning the multiple rows of rooms and tutors into + separated lists. I have used the code below in the past:

DECLARE @Tutors as varchar(8000)

SELECT @Tutors = isnull(@Tutors + ' + ', '') + name
FROM (
    SELECT CT_EVENT_STAFF.event_id, CT_EVENT_STAFF.weeks, 
        CT_STAFF.unique_name, CT_STAFF.name
    FROM celcat200809.dbo.CT_EVENT_STAFF AS CT_EVENT_STAFF 
    LEFT OUTER JOIN celcat200809.dbo.CT_STAFF AS CT_STAFF 
        ON CT_EVENT_STAFF.staff_id = CT_STAFF.staff_id
    WHERE event_id = @eventID
) As data_set

print @Tutors

The event_id being the unique event, this will only work when I know the exact ID, I can't run it for every ID.

Is there a way to do this for each individual event_id without cursors. I have seen a possible solution to this in using a UDF unfortunately my second problem is the timetabling system (CELCAT) creates a new database for each year (I know don't ask) so I am going to have to make the SQL dynamic i.e next years database would be celcat200910, I believe dynamic SQL cannot be run in UDF's.

Please remember this is SQL Server 2000

Answer 1

You could create an UDF to calculate the string, and then use it like:

select event_id, dbo.GetTutorsText(@eventId)
from EventsTable

The UDF could be defined like:

if object_id('dbo.GetTutorText') is not null 
    drop function dbo.GetTutorText
go
create function dbo.GetTutorText(
    @eventID int)
returns varchar(8000)
as
begin
DECLARE @Tutors as varchar(8000)

SELECT @Tutors = isnull(@Tutors + ' + ', '') + name
FROM (
    SELECT CT_EVENT_STAFF.event_id, CT_EVENT_STAFF.weeks, 
        CT_STAFF.unique_name, CT_STAFF.name
    FROM celcat200809.dbo.CT_EVENT_STAFF AS CT_EVENT_STAFF 
    LEFT OUTER JOIN celcat200809.dbo.CT_STAFF AS CT_STAFF 
        ON CT_EVENT_STAFF.staff_id = CT_STAFF.staff_id
    WHERE event_id = @eventID
) As data_set

return @Tutors
end
go

Answer 2

For your second problem, use a VIEW. Create a view to each table of interest in the celcat database and use the views instead.

When the database progresses to the following year, just update all of the views to point to the new database. Every query in the system using the VIEW will now address the correct database.

Answer 3

You can still use a view as goodgai suggested, but instead of having it redirect to one table, have it union select the tables together. Could break out the year/month into columns if that's not already done and you need it.

CREATE VIEW UNIFIED_CT_STAFF
AS
SELECT year = 2008, month = 9, unique_name, name FROM celcat200809.dbo.CT_STAFF
UNION SELECT year = 2008, month = 10, unique_name, name FROM celcat200810.dbo.CT_STAFF

Answer 4

• May I ask why you need to concatenate the names on the server? Couldn't the client application do this for you?

• If you are having trouble addressing tables in other databases, create views with standardized names, one per table, that simply select * from each table. You could write an SP that creates the views automatically, letting you pass in just the name of the database you want to set all the views to. The views won't hurt performance in any significant way.

• Since you're using a left join to CT_STAFF this makes me believe that the staff person may be missing, in which case you will lose data with your expression that concatenates them because it doesn't allow for a NULL staff name (it would reset the list each time a NULL staff name was encountered).

Here's a query that may do what you need, though it is a bit of a hack:

SELECT
   seqid = identity(int, 1, 1),
   event_id,
   S.name
INTO #EventNames
FROM
   celcat200809.dbo.CT_EVENT_STAFF ES
   LEFT JOIN celcat200809.dbo.CT_STAFF S ON ES.staff_id = S.staff_id
ORDER BY
   event_id,
   S.name --optional, whatever you like here.

SELECT
   EN.event_id,
   Max(CASE seqid - minseqid WHEN 0 THEN EN.name ELSE '' END))
   + Max(Coalesce(' + ' + CASE seqid - minseqid WHEN 1 THEN EN.name ELSE NULL END, ''))
   + Max(Coalesce(' + ' + CASE seqid - minseqid WHEN 2 THEN EN.name ELSE NULL END, ''))
   + Max(Coalesce(' + ' + CASE seqid - minseqid WHEN 3 THEN EN.name ELSE NULL END, ''))
   + Max(Coalesce(' + ' + CASE seqid - minseqid WHEN 4 THEN EN.name ELSE NULL END, ''))
   + Max(Coalesce(' + ' + CASE seqid - minseqid WHEN 5 THEN EN.name ELSE NULL END, ''))
   + Max(Coalesce(' + ' + CASE seqid - minseqid WHEN 6 THEN EN.name ELSE NULL END, ''))
   + Max(Coalesce(' + ' + CASE seqid - minseqid WHEN 7 THEN EN.name ELSE NULL END, ''))
   + Max(Coalesce(' + ' + CASE seqid - minseqid WHEN 8 THEN EN.name ELSE NULL END, ''))
   + Max(Coalesce(' + ' + CASE seqid - minseqid WHEN 9 THEN EN.name ELSE NULL END, ''))
   + Max(Coalesce(' + ' + CASE seqid - minseqid WHEN 10 THEN EN.name ELSE NULL END, ''))
FROM
   #EventNames EN
   INNER JOIN (
      SELECT event_id, minseqid = Min(seqid) FROM #EventNames GROUP BY event_id
   ) X ON EN.event_id = X.event_id
GROUP BY EN.event_id

Just make sure to put enough of those Max() expressions in to cover the greatest possible number of staff per event.

To get more data about the event, don't put it in the temp table (this will make it slower). Just use this big query as its own derived table and join back to the tables you need.

Answer 5

I've done a bit of celcat development over the past few months - it's a bit of a nightmare, and I sympathise with you!

To be honest, in this situation you would probably be better using the Celcat API (which takes a bit of getting used to, but is quite powerful and has the advantage that your queries should be fairly safe across versions.)

I created a class which I used to select specific database versions etc. creating a session specific to the academic year I wanted to use.

Within the API, there is also the option to directly run SQL if you need to.

I know this doesn't answer your question, but I hope it solves your problem!