Database

CREATE FUNCTION fn_SamplesTerminalPortWellLocationText
(
	@SampleID int
)
RETURNS varchar(1000)
AS
/*
	DROP FUNCTION fn_SamplesTerminalPortWellLocationText
	GRANT EXEC ON fn_SamplesTerminalPortWellLocationText TO xxxx

	select dbo.fn_SamplesTerminalPortWellLocationText(1) as test

	--GO
	DECLARE @SampleID int
	SELECT  @SampleID = 1
	Select  SampleID, sp_SamplesTerminalPortWellLocationText(SampleID)
	FROM	tblSamplesSample WHERE SampleID = @SampleID
*/
BEGIN

	DECLARE @RowCount int,
		@Debug bit,
		@Results varchar(1000)
	SELECT  @Debug = 0,
		@Results = ''

	-- Loop thru all of the terminal port and concantenate them together
	DECLARE @TerminalPortWellLocation varchar(75)

	DECLARE TPWL CURSOR FAST_FORWARD FOR
		SELECT	tp.TerminalPortWellLocation
		FROM 	tblSamplesSampleTerminalPortWellLocation stp,
			tblSamplesTerminalPortWellLocation tp
		WHERE	stp.TerminalPortWellLocationID = tp.TerminalPortWellLocationID
		AND	stp.SampleID = @SampleID
		AND	(stp.Deleted Is Null Or stp.Deleted = 0)
		ORDER BY TerminalPortWellLocation
	
	OPEN TPWL
	FETCH FROM TPWL 
	  INTO @TerminalPortWellLocation

	While @@Fetch_Status = 0
	BEGIN
		
		SELECT @Results = @Results + @TerminalPortWellLocation + ', '
	
		FETCH FROM TPWL 
	  	  INTO @TerminalPortWellLocation
	END
	-- Close & Deallocate Cursor
	Close TPWL
	Deallocate TPWL

	-- Trim the last comma
	-- Return the results
	IF (DataLength(@Results) > 2)
		SELECT @Results = SubString(@Results, 1, DataLength(@Results) - 2) 

	RETURN(@Results)

END

SELECT   tp.OrderBy, tp.PropertyTestID, Replace(tp.PropertyTestName, ',', '') AS PropertyTestName,
  SUM(CASE WHEN S.SampleID = 109 THEN ResultDecimal ELSE NULL END) AS [N/A109 03/31/2004],  
  SUM(CASE WHEN S.SampleID = 108 THEN ResultDecimal ELSE NULL END) AS [N/A108 03/30/2004],  
  SUM(CASE WHEN S.SampleID = 107 THEN ResultDecimal ELSE NULL END) AS [N/A107 03/17/2004],  
  SUM(CASE WHEN S.SampleID = 106 THEN ResultDecimal ELSE NULL END) AS [N/A106 03/01/2004],  
  SUM(CASE WHEN S.SampleID = 105 THEN ResultDecimal ELSE NULL END) AS [N/A105 01/05/2004],  
  SUM(CASE WHEN S.SampleID = 104 THEN ResultDecimal ELSE NULL END) AS [N/A104 11/17/2003]
FROM     tblSamplesPropertyTest tp, 
         ........[the rest of the query blah, blah blah...]

DROP TABLE tempdb..TestGUID
DROP TABLE tempdb..TestAutoID 
GO
CREATE TABLE tempdb..TestGUID (
	ID  uniqueidentifier ROWGUIDCOL Not NULL  DEFAULT (newid()),
	TestValue varchar(50)
) 


CREATE TABLE tempdb..TestAutoID (
	ID  int Identity (1, 1) Not Null,
	TestValue varchar(50)
) 
GO

INSERT INTO tempdb..TestGUID
( TestValue )
SELECT 'Test GUID'
SELECT @@Identity 

INSERT INTO tempdb..TestAutoID
( TestValue )
SELECT 'Test Integer ID'
SELECT @@Identity 

SELECT * FROM tempdb..TestGUID
SELECT * FROM tempdb..TestAutoID

(1 row(s) affected)

                                         
---------------------------------------- 
NULL

(1 row(s) affected)


(1 row(s) affected)

                                         
---------------------------------------- 
1

(1 row(s) affected)

ID                                   TestValue                                          
------------------------------------ -------------------------------------------------- 
E51F034D-BD69-4A34-9983-A2B1007FBA74 Test GUID

(1 row(s) affected)

ID          TestValue                                          
----------- -------------------------------------------------- 
1           Test Integer ID

(1 row(s) affected)