Hi I have this problem with a query to process data concatenating
Units/Modules.

I have one View called Certificates thats lists Course IDs, Candidate
Names and Units. As each Certificate shows multiple Units achieved by
the Candidate I need to transform this data and combine the Units into
a single value - so I have 1 row per certificate rathan than 1 row per
unit.

I have been using this Query and several UDF's (Unit names, Unit
Grades, Unit Award dates etc)  but as the database incresed in size the
query is timing out - Is there a more efficient way to do this ? Also
the function seems to create duplicates so i am using DISTINCT and
thisn seems to slow it down too. Help!

Thanks
hals_left

SELECT DISTINCT  CourseID, CandidateID,dbo.GetUnits(CandidateID,
CourseID) AS Units
FROM Certificates

CREATE FUNCTION dbo.GetUnits
(@CandidateID AS smallint, @CourseID AS smallint )
RETURNS varchar(1000)
AS
BEGIN
  DECLARE @str AS varchar(1000)
  SET @str = ''

SELECT @str = @str + UnitTitle + char(13) + char(10)
FROM Certificates
WHERE CandidateID = @CandidateID AND CourseID= @CourseID
RETURN @str
END

If this is just formatting for display then it isn't obvious why you
would need to do it in the database. The simplest and most likely
fastest method is in the client or presentation tier. See:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthgetstringmethod(recordset)ado.asp

I agree but the client application (Word 2K Mailmerge) isnt capable of
this.

Hello, hals_left

To make sure that the function is not called too many times, use a
query like this:

SELECT CourseID, CandidateID,
    dbo.GetUnits(CandidateID, CourseID) AS Units
FROM (
    SELECT DISTINCT  CourseID, CandidateID,
    FROM Certificates
) x

Razvan

Trasform columns to rows
Still struggling w/ LAST transaction date!!!
DISTINCT MonthName for a lot of dates....
Dynamic Stored Procedure
Sum() And Nulls