Database

<br />
drop table #1<br />
go<br />
SELECT DISTINCT TOP 3 -- Rows returned Will be determined by user<br />
	SC.SampleCondensateID, <br />
	IsNull(SC.TripNumber, '') + ' ' + convert(CHAR(10), SC.ShipDate, 110) as [ColumnName], <br />
	SC.ShipDate   --+ char(13)<br />
INTO 	#1<br />
FROM  	tblSampleCondensate SC,<br />
	tblSampleCondensateResult SCR,<br />
	tblSamplesCondensateType CT<br />
WHERE 	SC.SampleCondensateID = SCR.SampleCondensateID <br />
AND 	SC.CondensateTypeID *= CT.CondensateTypeID <br />
AND	(SC.Deleted = 0 or SC.Deleted IS NULL) <br />
<br />
<br />
/* SELECT * FROM #1 -- Results<br />
SampleCondensateID ColumnName        ShipDate  <br />
------------------ ----------------- -------------------<br />
37                 ABCD34 11-26-2003 2003-11-26 00:00:00<br />
38                 BCDE65 11-26-2003 2003-11-26 00:00:00<br />
54                 CDEF87 11-29-2003 2003-11-29 00:00:00<br />
*/<br />
<br />
DECLARE Properties CURSOR FAST_FORWARD FOR<br />
	Select SampleCondensateID, [ColumnName] <br />
	From #1<br />
	Order by ShipDate DESC<br />
	 <br />
Declare @SQL varchar(8000),<br />
	@InSQL varchar(200),<br />
	@ID varchar(10)<br />
SELECT 	@SQL='', @InSQL='', @ID=''<br />
<br />
Declare @ColumnName varchar(75),<br />
	@SampleCondensateID int<br />
<br />
Open Properties <br />
Fetch From Properties <br />
    Into @SampleCondensateID, @ColumnName<br />
<br />
-- Loop through each row in #1 to create the columns for the final query<br />
While @@Fetch_Status = 0<br />
BEGIN<br />
	-- Convert the Int value to a VarChar and trim extra spaces off<br />
	SELECT @ID = RTrim(Convert(varchar(10), @SampleCondensateID))<br />
	-- Build the In Clause<br />
	SELECT @InSQL = @InSQL + @ID +  + ','<br />
        -- Build the Column with [ColumnName] being its name<br />
	SELECT @SQL = @SQL + <br />
		+ ' SUM(CASE WHEN SampleCondensateID = ' + @ID <br />
		+ ' THEN Results ELSE 0 END) AS [' + @ColumnName + '], ' <br />
<br />
	Fetch From Properties<br />
	Into  @SampleCondensateID, @ColumnName<br />
END<br />
<br />
-- Close & Deallocate Cursor<br />
Close Properties<br />
Deallocate Properties<br />
<br />
-- Trim Commas From SQL Variables<br />
SELECT @InSQL = SubString(@InSQL, 1, DataLength(@InSQL) - 1)<br />
SELECT @SQL = SubString(@SQL, 1, DataLength(@SQL) - 2)<br />
<br />
-- Build & Execute the Select query<br />
Exec('Select TestProperty, ' + @SQL<br />
	+ 'From	tblSamplesTestProperty tp, <br />
		tblSampleCondensateResult cr<br />
	WHERE 	tp.TestPropertyID *= cr.AmineID<br />
	AND	cr.SampleCondensateID in ('<br />
	+	@InSQL<br />
	+ ') GROUP BY tp.TestProperty	Order By tp.TestProperty'<br />
     )<br />
<br />
/* Actual Query Statement<br />
<br />
SELECT  TestProperty,  <br />
        SUM(CASE WHEN SampleCondensateID = 55 THEN Results ELSE 0 END) AS [DEFG56 11-29-2003],  <br />
        SUM(CASE WHEN SampleCondensateID = 37 THEN Results ELSE 0 END) AS [ABCD34 11-26-2003],  <br />
        SUM(CASE WHEN SampleCondensateID = 38 THEN Results ELSE 0 END) AS [BCDE65 11-26-2003]<br />
FROM    tblSamplesTestProperty tp, <br />
        tblSampleCondensateResult cr<br />
WHERE   tp.TestPropertyID *= cr.AmineID<br />
AND     cr.SampleCondensateID in (54,37,38) <br />
GROUP BY tp.TestProperty	<br />
ORDER By tp.TestProperty<br />
<br />
*/<br />
/* Final Results<br />
<br />
TestProperty           CDEF87 11-29-2003 ABCD34 11-26-2003 BCDE65 11-26-2003<br />
---------------------- ----------------- ----------------- -----------------<br />
Cyclohexylamine        1.2340000          .0000000          .0000000<br />
Dibutylamine            .0000000         2.3400000         1.1110000<br />
Diethanolamine          .0000000          .0000000         3.0200000<br />
EDA+MEA                 .0000000         4.9100000          .0000000<br />
Morpholine              .0000000          .0000000          .0000000<br />
Triethanolamine         .0000000          .0000000          .0000000<br />
Unknowns As Morpholine 3.8820000         5.5500000         7.1920000<br />
<br />
*/<br />
<br />

EuroCPian Spring 2004 Get Together[^]